﻿using Mapster;
using Microsoft.Win32;
using Mysqlx.Crud;
using Newtonsoft.Json;
using Org.BouncyCastle.Bcpg.OpenPgp;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Drawing;
using System.Drawing.Drawing2D;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;
using 完工订单定时任务.http;
using 完工订单定时任务.models;
using 完工订单定时任务.models.ncdtomodel;
using 完工订单定时任务.models.plcmodel;
using 完工订单定时任务.models.sqlservermodel;

namespace 完工订单定时任务
{
    public partial class Form1 : Form
    {
        //日志
        NLog.Logger logger = NLog.LogManager.GetCurrentClassLogger();
        public Form1()
        {
            InitializeComponent();
        }

        private void button计算_Click(object sender, EventArgs e)
        {
            if (textBox产线.Text.Trim() == "1")
            {
                需计算工单DTO 需计算工单dto = 根据单号查完工工单数据(textBox单号.Text.Trim(), "scll");
                if (需计算工单dto != null)
                {
                    按时间计算(需计算工单dto.vbillcode, "1", 需计算工单dto.开工时间.ToString("yyyy-MM-dd HH:mm:ss"), 需计算工单dto.结束时间.ToString("yyyy-MM-dd HH:mm:ss"));
                }
                else
                {
                    textBoxMemo.Text = $"{textBox单号.Text.Trim()}PLC数据错误，请检查是不是有开工状态也有结束状态，并且结束时间在开始时间后面";
                }
            }
            else
            {
                需计算工单DTO 需计算工单dto = 根据单号查完工工单数据(textBox单号.Text.Trim(), "scll二线");
                if (需计算工单dto != null)
                {
                    按时间计算(需计算工单dto.vbillcode, "2", 需计算工单dto.开工时间.ToString("yyyy-MM-dd HH:mm:ss"), 需计算工单dto.结束时间.ToString("yyyy-MM-dd HH:mm:ss"));
                }
                else
                {
                    textBoxMemo.Text = $"{textBox单号.Text.Trim()}PLC数据错误，请检查是不是有开工状态也有结束状态，并且结束时间在开始时间后面";
                }
            }
            //计算(textBox单号.Text.Trim(),textBox产线.Text.Trim());
        }
        public 需计算工单DTO 按钮计算(string 产线, string 单号)
        {
            if (产线 == "1")
            {
                需计算工单DTO 需计算工单dto = 根据单号查完工工单数据(单号, "scll");
                return 需计算工单dto;
            }
            else
            {
                需计算工单DTO 需计算工单dto = 根据单号查完工工单数据(单号, "scll二线");
                return 需计算工单dto;
            }
        }

        private void 计算(string vbillcode, string lineno) {
            //string vbillcode = "";
            //string lineno = "";


            if (string.IsNullOrEmpty(vbillcode) == false && string.IsNullOrEmpty(lineno) == false)
            {
                if (lineno == "1")
                {

                    //查领料数据
                    List<scll> scll = SqlSugarHelp.scope.Queryable<scll>().Where(it => it.vbillcode == vbillcode && it.status == "结束").ToList();
                    if (scll == null || scll.Count <= 0)
                    {
                        textBoxMemo.Text = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "未查到该单号结束生产信息";
                        return;
                    }
                    else
                    {


                        vbillcode = scll[0].vbillcode;
                        lineno = scll[0].lineno;

                        //计算时先删除
                        string sqldelete = $@"
                        DELETE FROM ncmaterialoutdetail WHERE vbillcode = '{vbillcode}';
                        ";
                        int r = SqlSugarHelp.scope.Ado.ExecuteCommand(sqldelete);

                        计算辅挤明细(vbillcode, "scll");
                        计算ABR明细(vbillcode, "scll");
                        计算主挤明细(vbillcode, "scll");
                        //再把明细数据汇总到出库表
                        汇总到出库表(vbillcode);
                        //
                        查NC现存量并更新(vbillcode, lineno);

                    }
                }
                if (lineno == "2")
                {
                    //查领料数据
                    List<scll二线> scll = SqlSugarHelp.scope.Queryable<scll二线>().Where(it => it.vbillcode == vbillcode && it.status == "结束").ToList();
                    if (scll == null || scll.Count <= 0)
                    {
                        textBoxMemo.Text = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "未查到该单号结束生产信息";
                        return;
                    }
                    else
                    {
                        vbillcode = scll[0].vbillcode;
                        lineno = scll[0].lineno;

                        //计算时先删除
                        string sqldelete = $@"
                        DELETE FROM ncmaterialoutdetail WHERE vbillcode = '{vbillcode}';
                        ";
                        int r = SqlSugarHelp.scope.Ado.ExecuteCommand(sqldelete);
                        计算辅挤明细(vbillcode, "scll二线");
                        计算ABR明细(vbillcode, "scll二线");
                        计算主挤明细(vbillcode, "scll二线");
                        //再把明细数据汇总到出库表
                        汇总到出库表(vbillcode);
                        //
                        查NC现存量并更新(vbillcode, lineno);
                    }
                }
                //发送请求
                // string url = ConfigurationManager.AppSettings["clckurl"].ToString();
                // var data = new { vbillcode };
                //string result = HttpHelper.HttpPost(url, JsonConvert.SerializeObject(data));
                // textBoxMemo.Text = result;

            }
            else
            {
                textBoxMemo.Text = "请输入单号";
            }
        }
        private void 按时间计算(string vbillcode, string lineno, string 开始时间, string 结束时间)
        {
            logger.Info($"按时间计算{vbillcode}-{lineno}-{开始时间}-{结束时间}");

            if (string.IsNullOrEmpty(vbillcode) == false && string.IsNullOrEmpty(lineno) == false)
            {
                if (lineno == "1")
                {

                    //查领料数据
                    List<scll> scll = SqlSugarHelp.scope.Queryable<scll>().Where(it => it.vbillcode == vbillcode && it.status == "结束").ToList();
                    if (scll == null || scll.Count <= 0)
                    {
                        textBoxMemo.Text = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "未查到该单号结束生产信息";
                        return;
                    }
                    else
                    {


                        vbillcode = scll[0].vbillcode;
                        lineno = scll[0].lineno;

                        //计算时先删除
                        string sqldelete = $@"
                        DELETE FROM ncmaterialoutdetail WHERE vbillcode = '{vbillcode}';
                        ";
                        int r = SqlSugarHelp.scope.Ado.ExecuteCommand(sqldelete);

                        按时间计算辅挤明细(vbillcode, "scll", 开始时间, 结束时间);
                        按时间计算ABR明细(vbillcode, "scll", 开始时间, 结束时间);
                        按时间计算主挤明细(vbillcode, "scll", 开始时间, 结束时间);
                        //再把明细数据汇总到出库表
                        汇总到出库表(vbillcode);
                        //
                        查NC现存量并更新(vbillcode, lineno);

                    }
                }
                if (lineno == "2")
                {
                    //查领料数据
                    List<scll二线> scll = SqlSugarHelp.scope.Queryable<scll二线>().Where(it => it.vbillcode == vbillcode && it.status == "结束").ToList();
                    if (scll == null || scll.Count <= 0)
                    {
                        textBoxMemo.Text = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "未查到该单号结束生产信息";
                        return;
                    }
                    else
                    {
                        vbillcode = scll[0].vbillcode;
                        lineno = scll[0].lineno;

                        //计算时先删除
                        string sqldelete = $@"
                        DELETE FROM ncmaterialoutdetail WHERE vbillcode = '{vbillcode}';
                        ";
                        int r = SqlSugarHelp.scope.Ado.ExecuteCommand(sqldelete);
                        按时间计算辅挤明细(vbillcode, "scll二线", 开始时间, 结束时间);
                        按时间计算ABR明细(vbillcode, "scll二线", 开始时间, 结束时间);
                        按时间计算主挤明细(vbillcode, "scll二线", 开始时间, 结束时间);
                        //再把明细数据汇总到出库表
                        汇总到出库表(vbillcode);
                        //
                        查NC现存量并更新(vbillcode, lineno);
                    }
                }
                //发送请求
                string url = ConfigurationManager.AppSettings["clckurl"].ToString();
                var data = new { vbillcode };
                string result = HttpHelper.HttpPost(url, JsonConvert.SerializeObject(data));
                logger.Info(vbillcode + "-" + result);
                textBoxMemo.Text = result;

            }
            else
            {
                textBoxMemo.Text = "请输入单号";
            }
        }
        private void 按时间计算1号单(string vbillcode, string lineno, string 开始时间, string 结束时间)
        {
            logger.Info($"按时间计算{vbillcode}-{lineno}-{开始时间}-{结束时间}");

            if (string.IsNullOrEmpty(vbillcode) == false && string.IsNullOrEmpty(lineno) == false)
            {
                if (lineno == "1")
                {
                    //计算时先删除
                    string sqldelete = $@"
                        DELETE FROM ncmaterialoutdetail WHERE vbillcode = '{vbillcode}';
                        ";
                    int r = SqlSugarHelp.scope.Ado.ExecuteCommand(sqldelete);

                    按时间计算1号单辅挤明细(vbillcode, "scll", 开始时间, 结束时间);
                    按时间计算1号单ABR明细(vbillcode, "scll", 开始时间, 结束时间);
                    按时间计算1号单主挤明细(vbillcode, "scll", 开始时间, 结束时间);
                    //再把明细数据汇总到出库表
                    汇总到出库表(vbillcode);
                    //
                    查NC现存量并更新(vbillcode, lineno);

                }
                if (lineno == "2")
                {
                    //计算时先删除
                    string sqldelete = $@"
                        DELETE FROM ncmaterialoutdetail WHERE vbillcode = '{vbillcode}';
                        ";
                    int r = SqlSugarHelp.scope.Ado.ExecuteCommand(sqldelete);
                    按时间计算1号单辅挤明细(vbillcode, "scll二线", 开始时间, 结束时间);
                    按时间计算1号单ABR明细(vbillcode, "scll二线", 开始时间, 结束时间);
                    按时间计算1号单主挤明细(vbillcode, "scll二线", 开始时间, 结束时间);
                    //再把明细数据汇总到出库表
                    汇总到出库表(vbillcode);
                    //
                    查NC现存量并更新(vbillcode, lineno);

                }
            }
            else
            {
                textBoxMemo.Text = "请输入单号";
            }
        }
        private void button开始任务_Click(object sender, EventArgs e)
        {
            if (int.TryParse(textBox分钟.Text, out int time) == true)
            {
                this.timer正常单号.Interval = time * 60 * 1000;
                this.timer正常单号.Enabled = true;
            }
            else
            {
                this.timer正常单号.Enabled = false;
                MessageBox.Show("请输入正确的时间");
            }

        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            try
            {
                timer正常单号.Enabled = false;
                logger.Info($"{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}开始任务生产用料");
                //把错误的塞到表
                //string sql塞材料数量中间变小记录 = @"insert into 材料数量中间变小记录 (guid,vbillcode,lineno,TankName,current_bus_date,current_quantity,previous_bus_date,previous_quantity,diff)
                //                                    SELECT UUID(),
                //                                        current.vbillcode,
                //                                        current.lineno,
                //                                        current.TankName,
                //                                        current.bus_date AS current_bus_date,
                //                                        ifnull(current.quantity, 0) +0 AS current_quantity,
                //                                        previous.bus_date AS previous_bus_date,
                //                                        ifnull(previous.quantity, 0) +0 AS previous_quantity,
                //                                        (ifnull(previous.quantity, 0) +0) - (ifnull(current.quantity, 0) +0) as diff
                //                                    FROM 
                //                                        scll  AS current
                //                                    JOIN 
                //                                        scll AS previous
                //                                    ON 
                //                                        current.vbillcode = previous.vbillcode   -- vbillcode 相同
                //                                        AND current.TankName = previous.TankName -- TankName 相同
                //                                        AND current.bus_date > previous.bus_date -- 当前记录的时间大于之前记录的时间
                //                                    WHERE 
                //                                        ifnull(current.quantity, 0) +0 <ifnull(previous.quantity, 0) +0   -- 当前的quantity比之前的小
		              //                                      and  (ifnull(previous.quantity, 0) +0) - (ifnull(current.quantity, 0) +0)  >1
		              //                                      and   current.bus_date >= '2024-10-01 07:26:17'
		              //                                      and  not EXISTS(select 1 from 材料数量中间变小记录 WHERE 材料数量中间变小记录.vbillcode = current.vbillcode)
		              //                                      and   EXISTS(select 1 from scll WHERE STATUS = '结束' and scll.vbillcode = current.vbillcode)
                //                                    ORDER BY 
                //                                        current.vbillcode, current.TankName, current.bus_date";

                string sql塞材料数量中间变小记录 = @"INSERT INTO 材料数量中间变小记录 (
                                                    guid, vbillcode, lineno, TankName,
                                                    current_bus_date, current_quantity,
                                                    previous_bus_date, previous_quantity, diff
                                                )
                                                WITH ranked_data AS (
                                                    SELECT
                                                        vbillcode,
                                                        lineno,
                                                        TankName,
                                                        bus_date AS current_bus_date,
                                                        IFNULL(quantity, 0) AS current_quantity,
                                                        LAG(bus_date) OVER (PARTITION BY vbillcode, TankName ORDER BY bus_date) AS previous_bus_date,
                                                        LAG(IFNULL(quantity, 0)) OVER (PARTITION BY vbillcode, TankName ORDER BY bus_date) AS previous_quantity
                                                    FROM
                                                        scll
                                                ),
                                                filtered_data AS (
                                                    SELECT
                                                        vbillcode,
                                                        lineno,
                                                        TankName,
                                                        current_bus_date,
                                                        current_quantity,
                                                        previous_bus_date,
                                                        previous_quantity,
                                                        previous_quantity - current_quantity AS diff
                                                    FROM
                                                        ranked_data
                                                    WHERE
                                                        current_quantity < previous_quantity
                                                        AND previous_quantity - current_quantity > 1
                                                        AND current_bus_date >= '2024-10-01 07:26:17'
                                                )

                                                SELECT
                                                     UUID(), vbillcode, lineno, TankName,
                                                    current_bus_date,  CASE 
        WHEN TRIM(current_quantity) = '' OR current_quantity IS NULL 
        THEN 0 
        ELSE current_quantity END AS current_quantity,
                                                    previous_bus_date, CASE 
        WHEN TRIM(previous_quantity) = '' OR previous_quantity IS NULL 
        THEN 0 
        ELSE previous_quantity END AS previous_quantity , diff
                                                FROM
                                                    filtered_data f
                                                WHERE
                                                    NOT EXISTS (
                                                        SELECT 1 FROM 材料数量中间变小记录 r
                                                        WHERE r.vbillcode = f.vbillcode
                                                    )
                                                    AND EXISTS (
                                                        SELECT 1 FROM scll s
                                                        WHERE s.vbillcode = f.vbillcode AND s.STATUS = '结束'
                                                    );

                                        ";

                int r塞材料数量中间变小记录 = SqlSugarHelp.scope.Ado.ExecuteCommand(sql塞材料数量中间变小记录);

                //sql塞材料数量中间变小记录 = @"insert into 材料数量中间变小记录 (guid,vbillcode,lineno,TankName,current_bus_date,current_quantity,previous_bus_date,previous_quantity,diff)
                //                            SELECT UUID(),
                //                                current.vbillcode,
                //                                current.lineno,
                //                                current.TankName,
                //                                current.bus_date AS current_bus_date,
                //                                ifnull(current.quantity, 0) +0 AS current_quantity,
                //                                previous.bus_date AS previous_bus_date,
                //                                ifnull(previous.quantity, 0) +0 AS previous_quantity,
                //                                (ifnull(previous.quantity, 0) +0) - (ifnull(current.quantity, 0) +0) as diff
                //                            FROM 
                //                                scll二线  AS current
                //                            JOIN 
                //                                scll二线 AS previous
                //                            ON 
                //                                current.vbillcode = previous.vbillcode   -- vbillcode 相同
                //                                AND current.TankName = previous.TankName -- TankName 相同
                //                                AND current.bus_date > previous.bus_date -- 当前记录的时间大于之前记录的时间
                //                            WHERE 
                //                                ifnull(current.quantity, 0) +0 <ifnull(previous.quantity, 0) +0   -- 当前的quantity比之前的小
		              //                              and  (ifnull(previous.quantity, 0) +0) - (ifnull(current.quantity, 0) +0)  >1
		              //                              and   current.bus_date >= '2024-10-01 07:26:17'
		              //                              and  not EXISTS(select 1 from 材料数量中间变小记录 WHERE 材料数量中间变小记录.vbillcode = current.vbillcode)
		              //                              and   EXISTS(select 1 from scll二线 WHERE STATUS = '结束' and scll二线.vbillcode = current.vbillcode)
                //                            ORDER BY 
                //                                current.vbillcode, current.TankName, current.bus_date";


                sql塞材料数量中间变小记录 = @"INSERT INTO 材料数量中间变小记录 (
                                                    guid, vbillcode, lineno, TankName,
                                                    current_bus_date, current_quantity,
                                                    previous_bus_date, previous_quantity, diff
                                                )
                                                WITH ranked_data AS (
                                                    SELECT
                                                        vbillcode,
                                                        lineno,
                                                        TankName,
                                                        bus_date AS current_bus_date,
                                                        IFNULL(quantity, 0) AS current_quantity,
                                                        LAG(bus_date) OVER (PARTITION BY vbillcode, TankName ORDER BY bus_date) AS previous_bus_date,
                                                        LAG(IFNULL(quantity, 0)) OVER (PARTITION BY vbillcode, TankName ORDER BY bus_date) AS previous_quantity
                                                    FROM
                                                        scll二线
                                                ),
                                                filtered_data AS (
                                                    SELECT
                                                        vbillcode,
                                                        lineno,
                                                        TankName,
                                                        current_bus_date,
                                                        current_quantity,
                                                        previous_bus_date,
                                                        previous_quantity,
                                                        previous_quantity - current_quantity AS diff
                                                    FROM
                                                        ranked_data
                                                    WHERE
                                                        current_quantity < previous_quantity
                                                        AND previous_quantity - current_quantity > 1
                                                        AND current_bus_date >= '2024-10-01 07:26:17'
                                                )

                                                SELECT
                                                    UUID(), vbillcode, lineno, TankName,
                                                    current_bus_date, current_quantity,
                                                    previous_bus_date, previous_quantity, diff
                                                FROM
                                                    filtered_data f
                                                WHERE
                                                    NOT EXISTS (
                                                        SELECT 1 FROM 材料数量中间变小记录 r
                                                        WHERE r.vbillcode = f.vbillcode
                                                    )
                                                    AND EXISTS (
                                                        SELECT 1 FROM scll二线 s
                                                        WHERE s.vbillcode = f.vbillcode AND s.STATUS = '结束'
                                                    );

                                        ";

                r塞材料数量中间变小记录 = SqlSugarHelp.scope.Ado.ExecuteCommand(sql塞材料数量中间变小记录);

                //查一线scll已经结束的工单
                string sql = $@"select distinct vbillcode from scll where status = '结束' and bus_date >= '2024-10-01 07:26:17'
                            and not EXISTS (select 1 from ncmaterialout where ncmaterialout.resultcode = '1' 
											and ncmaterialout.vbillcode = scll.vbillcode)
                            and not EXISTS(select 1 from 材料数量中间变小记录 where 材料数量中间变小记录.vbillcode = scll.vbillcode) and vbillcode <> '1'";
                List<scll> sclls = SqlSugarHelp.scope.Ado.SqlQuery<scll>(sql);

                if (sclls != null && sclls.Count > 0)
                {
                    foreach (var item in sclls)
                    {
                        需计算工单DTO 需计算工单dto = 根据单号查完工工单数据(item.vbillcode, "scll");
                        if (需计算工单dto != null)
                        {
                            按时间计算(需计算工单dto.vbillcode, "1", 需计算工单dto.开工时间.ToString("yyyy-MM-dd HH:mm:ss"), 需计算工单dto.结束时间.ToString("yyyy-MM-dd HH:mm:ss"));
                        }
                    }
                }
                //查二线scll已经结束的工单
                string sql2 = $@"select distinct vbillcode from scll二线 where status = '结束' and bus_date > '2024-10-01 07:57:19'
                            and not EXISTS (select 1 from ncmaterialout where ncmaterialout.resultcode = '1'
                                            and ncmaterialout.vbillcode = scll二线.vbillcode)
                            and not EXISTS(select 1 from 材料数量中间变小记录 where 材料数量中间变小记录.vbillcode = scll二线.vbillcode) and vbillcode <> '1'";
                List<scll二线> scll二线s = SqlSugarHelp.scope.Ado.SqlQuery<scll二线>(sql2);
                if (scll二线s != null && scll二线s.Count > 0)
                {
                    foreach (var item in scll二线s)
                    {
                        需计算工单DTO 需计算工单dto = 根据单号查完工工单数据(item.vbillcode, "scll二线");
                        if (需计算工单dto != null)
                        {
                            按时间计算(需计算工单dto.vbillcode, "2", 需计算工单dto.开工时间.ToString("yyyy-MM-dd HH:mm:ss"), 需计算工单dto.结束时间.ToString("yyyy-MM-dd HH:mm:ss"));
                        }
                    }
                }

                //查一线的1号工单
                //查一线1号工单();
                //查二线1号工单();

                logger.Info($"{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}结束任务生产用料");
            }
            catch (Exception ex)
            {
                logger.Info($"生产用料材料计算任务异常{ex.StackTrace}");
            }
            finally {
                timer正常单号.Enabled = true;
                label材料计算.Text = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            }

        }

        private void 查一线1号工单()
        {
            string sql = $@"WITH ordered_scll AS (
                                SELECT DISTINCT 
                                    vbillcode,
                                    status,
                                    DATE_FORMAT(bus_date,'%Y-%m-%d %H:%i')bus_date
                                FROM 
                                    scll
                                WHERE 
                                    vbillcode = 1
                                ORDER BY 
                                    DATE_FORMAT(bus_date,'%Y-%m-%d %H:%i')
                            ),
                            segment_pairs AS (
                                SELECT 
                                    DATE_FORMAT(start.bus_date,'%Y-%m-%d %H:%i') AS 开工时间,
                                    MIN(DATE_FORMAT(end.bus_date,'%Y-%m-%d %H:%i')) AS 结束时间
                                FROM 
                                    ordered_scll start
                                LEFT JOIN ordered_scll end
                                    ON start.vbillcode = end.vbillcode
                                    AND start.status = '开工'
                                    AND end.status = '结束'
                                    AND DATE_FORMAT(end.bus_date,'%Y-%m-%d %H:%i') > DATE_FORMAT(start.bus_date,'%Y-%m-%d %H:%i')
                                WHERE 
                                    start.status = '开工'
                                GROUP BY 
                                    DATE_FORMAT(start.bus_date,'%Y-%m-%d %H:%i')
                            ),
                            final_segments AS (
                                SELECT 
                                    开工时间,
                                    COALESCE(结束时间, (SELECT MAX( DATE_FORMAT(bus_date,'%Y-%m-%d %H:%i')) 
                                                        FROM scll 
                                                        WHERE vbillcode = 1 
                                                          AND  DATE_FORMAT(bus_date,'%Y-%m-%d %H:%i') > start.开工时间)) AS 结束时间
                                FROM 
                                    segment_pairs start
                            )
                            SELECT 
                                str_to_date(开工时间, '%Y-%m-%d %H:%i:%S') 开工时间,
                                str_to_date(结束时间, '%Y-%m-%d %H:%i:%S')结束时间
                            FROM 
                                final_segments
                            ORDER BY 
                                开工时间;
                            ";
            List<需计算工单DTO> 需计算工单dto = SqlSugarHelp.scope.Ado.SqlQuery<需计算工单DTO>(sql);

            if (需计算工单dto != null && 需计算工单dto.Count > 0)
            {
                foreach (var item in 需计算工单dto)
                {
                    string 加时间一号单 = 1 + "-line1-" + item.开工时间.ToString("yyyy-MM-dd HH:mm") + "-" + item.结束时间.ToString("yyyy-MM-dd HH:mm");
                    按时间计算1号单(加时间一号单, "1", item.开工时间.ToString("yyyy-MM-dd HH:mm:ss"), item.结束时间.ToString("yyyy-MM-dd HH:mm:ss"));
                }
            }
        }

        private void 查二线1号工单()
        {
            string sql = $@"WITH ordered_scll AS (
                                SELECT DISTINCT 
                                    vbillcode,
                                    status,
                                    DATE_FORMAT(bus_date,'%Y-%m-%d %H:%i')bus_date
                                FROM 
                                    scll二线
                                WHERE 
                                    vbillcode = 1
                                ORDER BY 
                                    DATE_FORMAT(bus_date,'%Y-%m-%d %H:%i')
                            ),
                            segment_pairs AS (
                                SELECT 
                                    DATE_FORMAT(start.bus_date,'%Y-%m-%d %H:%i') AS 开工时间,
                                    MIN(DATE_FORMAT(end.bus_date,'%Y-%m-%d %H:%i')) AS 结束时间
                                FROM 
                                    ordered_scll start
                                LEFT JOIN ordered_scll end
                                    ON start.vbillcode = end.vbillcode
                                    AND start.status = '开工'
                                    AND end.status = '结束'
                                    AND DATE_FORMAT(end.bus_date,'%Y-%m-%d %H:%i') > DATE_FORMAT(start.bus_date,'%Y-%m-%d %H:%i')
                                WHERE 
                                    start.status = '开工'
                                GROUP BY 
                                    DATE_FORMAT(start.bus_date,'%Y-%m-%d %H:%i')
                            ),
                            final_segments AS (
                                SELECT 
                                    开工时间,
                                    COALESCE(结束时间, (SELECT MAX( DATE_FORMAT(bus_date,'%Y-%m-%d %H:%i')) 
                                                        FROM scll二线 
                                                        WHERE vbillcode = 1 
                                                          AND  DATE_FORMAT(bus_date,'%Y-%m-%d %H:%i') > start.开工时间)) AS 结束时间
                                FROM 
                                    segment_pairs start
                            )
                            SELECT 
                                str_to_date(开工时间, '%Y-%m-%d %H:%i:%S') 开工时间,
                                str_to_date(结束时间, '%Y-%m-%d %H:%i:%S')结束时间
                            FROM 
                                final_segments
                            ORDER BY 
                                开工时间;
                            ";
            List<需计算工单DTO> 需计算工单dto = SqlSugarHelp.scope.Ado.SqlQuery<需计算工单DTO>(sql);

            if (需计算工单dto != null && 需计算工单dto.Count > 0)
            {
                foreach (var item in 需计算工单dto)
                {
                    string 加时间一号单 = 1 + "-line2-" + item.开工时间.ToString("yyyy-MM-dd HH:mm") + "-" + item.结束时间.ToString("yyyy-MM-dd HH:mm");
                    按时间计算1号单(加时间一号单, "2", item.开工时间.ToString("yyyy-MM-dd HH:mm:ss"), item.结束时间.ToString("yyyy-MM-dd HH:mm:ss"));
                }
            }
        }

        private List<需计算工单DTO> 查完工工单数据(string tableName)
        {
            //查出开工和结束的数据
            string sql = $@"SELECT 
                                    s2.vbillcode,
                                    s1.status AS 开工状态,
                                    s2.status AS 结束状态,
                                    MIN(s1.bus_date) AS 开工时间,
                                    MAX(s2.bus_date) AS 结束时间
                                FROM 
                                    {tableName} s1
                                JOIN 
                                    {tableName} s2 
                                ON 
                                    s1.vbillcode = s2.vbillcode   -- 关联相同的vbillcode
                                    AND s1.status = '开工'         -- s1是开工状态
                                    AND s2.status = '结束'         -- s2是结束状态
                                    AND s1.bus_date < s2.bus_date  -- 开工的bus_date小于结束的bus_date
                                    where  not EXISTS (select 1 from ncmaterialout where vbillcode = s1.vbillcode and resultcode = '1')
                                GROUP BY 
                                    s2.vbillcode, s1.status, s2.status  -- 按vbillcode分组
                                ORDER BY 
                                    s2.vbillcode;";
            List<需计算工单DTO> dtos = SqlSugarHelp.scope.Ado.SqlQuery<需计算工单DTO>(sql);
            return dtos;
        }

        private 需计算工单DTO 根据单号查完工工单数据(string vbillcode, string tableName)
        {
            //查出开工和结束的数据
            string sql = $@"SELECT 
                                    s2.vbillcode,
                                    s1.status AS 开工状态,
                                    s2.status AS 结束状态,
                                    MIN(s1.bus_date) AS 开工时间,
                                    MAX(s2.bus_date) AS 结束时间
                                FROM 
                                    {tableName} s1
                                JOIN 
                                    {tableName} s2 
                                ON 
                                    s1.vbillcode = s2.vbillcode   -- 关联相同的vbillcode
                                    AND s1.status = '开工'         -- s1是开工状态
                                    AND s2.status = '结束'         -- s2是结束状态
                                    AND s1.bus_date < s2.bus_date  -- 开工的bus_date小于结束的bus_date
                                    where  not EXISTS (select 1 from ncmaterialout where vbillcode = s1.vbillcode and resultcode = '1')
                                    and s1.vbillcode = '{vbillcode}'
                                GROUP BY 
                                    s2.vbillcode, s1.status, s2.status  -- 按vbillcode分组
                                ORDER BY 
                                    s2.vbillcode;";
            需计算工单DTO dtos = SqlSugarHelp.scope.Ado.SqlQuery<需计算工单DTO>(sql).FirstOrDefault();
            return dtos;
        }

        public int 计算辅挤明细(string vbillcode, string tableName)
        {
            logger.Info("开始计算辅挤明细");
            ////计算时先删除
            //string sqldelete = $@"
            //            DELETE FROM ncmaterialoutdetail WHERE vbillcode = '{vbillcode}';
            //            ";
            //int r = SqlSugarHelp.scope.Ado.ExecuteCommand(sqldelete);
            string sql = $@"
                        INSERT into ncmaterialoutdetail(guid,vbillcode,lineno,current_materialName,TankName,current_quantity,previous_quantity,quantity_diff,bus_date)
                             SELECT  UUID(),
                            current_row.vbillcode,
                                current_row.lineno,
						                    CASE 
                        WHEN current_row.row_num = 1 THEN current_row.materialName 
		                     ELSE previous_row.materialName
		                     end 
                             AS current_materialName,
                            current_row.TankName,
                            ifnull(current_row.quantity, 0) +0 AS current_quantity,
                           ifnull(previous_row.quantity, 0) +0 AS previous_quantity,

                       CASE 
                        WHEN current_row.row_num = 1 THEN current_row.quantity 
                        ELSE (ifnull((current_row.quantity - previous_row.quantity ), 0)+0)
                    END * 
                    CASE 
                        WHEN current_row.row_num = 1 THEN current_row.Ratio 
                        ELSE previous_row.Ratio
                    END 
                    /100
                             AS quantity_diff,

                            current_row.bus_date
                        FROM 
                            (SELECT 
                                status,
                                 vbillcode, 
				                         lineno,
                                 materialName, 
                                 quantity, 
                                  TankName, 
                                 bus_date, 
                                Ratio,
                                 ROW_NUMBER() OVER (PARTITION BY TankName ORDER BY bus_date ) AS row_num
                             FROM {tableName} where vbillcode = '{vbillcode}' 
                            and quantity+0 >0
                            and status <> '换产'	
                        --  and status in ('完工','结束','开工')
                                and TankName not like '%主挤%'
                                and TankName not in ('主挤_ABR_S1','主挤_ABR_S2','主挤_ABR_S3') )  AS current_row
                        LEFT JOIN 
                            (SELECT 
                                status,
                                 vbillcode, 
				                         lineno,
                                 materialName, 
                                 quantity, 
                                 TankName, 
                                 bus_date, 
                                    Ratio,
                                 ROW_NUMBER() OVER (PARTITION BY TankName ORDER BY bus_date ) AS row_num
                             FROM {tableName} where vbillcode = '{vbillcode}'
                            and quantity+0 >0
                            and status <> '换产'	
                        --  and STATUS in ('完工','结束','开工')
                                and TankName not like '%主挤%'
                                and TankName not in ('主挤_ABR_S1','主挤_ABR_S2','主挤_ABR_S3')) AS previous_row
                        ON current_row.TankName = previous_row.TankName 
                           AND current_row.row_num = previous_row.row_num + 1
                        ORDER BY current_row.TankName, current_row.bus_date DESC;

                            ";

            int r2 = SqlSugarHelp.scope.Ado.ExecuteCommand(sql);
            logger.Info("结束计算辅挤明细，计算结果行数：" + r2);
            return r2;
        }
        public int 计算主挤明细(string vbillcode, string tableName)
        {
            logger.Info("开始计算明细");
            ////计算时先删除
            //string sqldelete = $@"
            //            DELETE FROM ncmaterialoutdetail WHERE vbillcode = '{vbillcode}';
            //            ";
            //int r = SqlSugarHelp.scope.Ado.ExecuteCommand(sqldelete);
            string sql = $@"
                        INSERT into ncmaterialoutdetail(guid,vbillcode,lineno,current_materialName,TankName,current_quantity,previous_quantity,quantity_diff,bus_date)
                            SELECT  UUID(),
                                current_row.vbillcode,
		                            current_row.lineno,
                                current_row.materialName AS current_materialName,
                                current_row.TankName,
                                ifnull(current_row.quantity, 0) +0 AS current_quantity,
                               ifnull(previous_row.quantity, 0) +0 AS previous_quantity,
	                             CASE 
                                    WHEN current_row.row_num = 1 THEN current_row.quantity
                                    ELSE ifnull((current_row.quantity - previous_row.quantity  ), 0)+0
                                END AS quantity_diff,
		
                                current_row.bus_date
                            FROM 
                                (SELECT 
                                     vbillcode, 
                                             lineno,
                                     materialName, 
                                     quantity, 
                                     TankName, 
                                     bus_date, 
                                    Ratio,
                                     ROW_NUMBER() OVER (PARTITION BY TankName ORDER BY bus_date ) AS row_num
                                 FROM {tableName} where vbillcode = '{vbillcode}' 
                                and quantity+0 >0
                                and status <> '换产'	
                            --  and status in ('完工','结束','开工')
                                    and TankName    like '%主挤%'
                                    and TankName not in ('主挤_ABR_S1','主挤_ABR_S2','主挤_ABR_S3','主挤_D') 
			                            )  AS current_row
                            LEFT JOIN 
                                (SELECT 
                                     vbillcode, 
                                             lineno,
                                     materialName, 
                                     quantity, 
                                    TankName, 
                                     bus_date, 
                                        Ratio,
                                     ROW_NUMBER() OVER (PARTITION BY TankName ORDER BY bus_date ) AS row_num
                                 FROM {tableName} where vbillcode = '{vbillcode}'
                                 and quantity+0 >0
                                and status <> '换产'	
                            --  and STATUS in ('完工','结束','开工')
                                    and TankName   like '%主挤%'
                                    and TankName not in ('主挤_ABR_S1','主挤_ABR_S2','主挤_ABR_S3','主挤_D')
				                            ) AS previous_row
                            ON current_row.TankName = previous_row.TankName 
                               AND current_row.row_num = previous_row.row_num + 1
                            ORDER BY current_row.TankName, current_materialName, current_row.bus_date DESC;
                        
                            ";

            int r2 = SqlSugarHelp.scope.Ado.ExecuteCommand(sql);
            logger.Info("结束计算明细，计算结果行数：" + r2);
            return r2;
        }

        public int 计算ABR明细(string vbillcode, string tableName)
        {
            logger.Info("开始计算明细");

            string sql = $@"
                        INSERT into ncmaterialoutdetail(guid,vbillcode,lineno,current_materialName,TankName,current_quantity,previous_quantity,quantity_diff,bus_date)

                         SELECT  UUID(),
                            current_row.vbillcode,
                                current_row.lineno,
						                    CASE 
                        WHEN current_row.row_num = 1 THEN current_row.materialName 
		                     ELSE previous_row.materialName
		                     end 
                             AS current_materialName,
                            current_row.TankName,
                            ifnull(current_row.quantity, 0) +0 AS current_quantity,
                           ifnull(previous_row.quantity, 0) +0 AS previous_quantity,

                       CASE 
                        WHEN current_row.row_num = 1 THEN current_row.quantity 
                        ELSE (ifnull((current_row.quantity - previous_row.quantity ), 0)+0)
                    END * 
                    CASE 
                        WHEN current_row.row_num = 1 THEN current_row.Ratio 
                        ELSE previous_row.Ratio
                    END 
                    /100
                             AS quantity_diff,

                            current_row.bus_date
                        FROM 
                            (SELECT status,
                                 vbillcode, 
				                         lineno,
                                 materialName, 
                                 quantity, 
                                 TankName, 
                                 bus_date, 
                                 Ratio,
                                 ROW_NUMBER() OVER (PARTITION BY TankName ORDER BY bus_date ) AS row_num
                             FROM {tableName} where vbillcode = '{vbillcode}' 
                            and quantity+0 >0
                            and status <> '换产'	
                            -- and status in ('完工','结束','开工')
                                and TankName  in ('主挤_ABR_S1','主挤_ABR_S2','主挤_ABR_S3') )  AS current_row
                        LEFT JOIN 
                            (SELECT status,
                                 vbillcode, 
				                         lineno,
                                 materialName, 
                                 quantity, 
                                 TankName, 
                                 bus_date, 
                                 Ratio,
                                 ROW_NUMBER() OVER (PARTITION BY TankName ORDER BY bus_date ) AS row_num
                             FROM {tableName} where vbillcode = '{vbillcode}'
                                and quantity+0 >0
                                and status <> '换产'	
                            -- and STATUS in ('完工','结束','开工')
                                and TankName  in ('主挤_ABR_S1','主挤_ABR_S2','主挤_ABR_S3')) AS previous_row
                        ON current_row.TankName = previous_row.TankName 
                           AND current_row.row_num = previous_row.row_num + 1
                        ORDER BY current_row.TankName, current_row.bus_date DESC;

                            ";

            int r2 = SqlSugarHelp.scope.Ado.ExecuteCommand(sql);
            logger.Info("结束计算明细，计算结果行数：" + r2);
            return r2;
        }


        public int 按时间计算辅挤明细(string vbillcode, string tableName, string 开始时间, string 结束时间)
        {
            logger.Info("开始计算明细");
            ////计算时先删除
            //string sqldelete = $@"
            //            DELETE FROM ncmaterialoutdetail WHERE vbillcode = '{vbillcode}';
            //            ";
            //int r = SqlSugarHelp.scope.Ado.ExecuteCommand(sqldelete);
            string sql = $@"
                        INSERT into ncmaterialoutdetail(guid,vbillcode,lineno,current_materialName,TankName,current_quantity,previous_quantity,quantity_diff,bus_date)
                             SELECT  UUID(),
                            current_row.vbillcode,
                                current_row.lineno,
						                    CASE 
                        WHEN current_row.row_num = 1 THEN current_row.materialName 
		                     ELSE previous_row.materialName
		                     end 
                             AS current_materialName,
                            current_row.TankName,
                            ifnull(current_row.quantity, 0) +0 AS current_quantity,
                           ifnull(previous_row.quantity, 0) +0 AS previous_quantity,

                       CASE 
                        WHEN current_row.row_num = 1 THEN current_row.quantity 
                        ELSE (ifnull((current_row.quantity - previous_row.quantity ), 0)+0)
                    END * 
                    CASE 
                        WHEN current_row.row_num = 1 THEN current_row.Ratio 
                        ELSE previous_row.Ratio
                    END 
                    /100
                             AS quantity_diff,

                            current_row.bus_date
                        FROM 
                            (SELECT 
                                status,
                                 vbillcode, 
				                         lineno,
                                 materialName, 
                                 quantity, 
                                  TankName, 
                                 bus_date, 
                                Ratio,
                                 ROW_NUMBER() OVER (PARTITION BY TankName ORDER BY bus_date ) AS row_num
                             FROM {tableName} where vbillcode = '{vbillcode}' 
                            and quantity+0 >0
                            and bus_date >= '{开始时间}' and bus_date <= '{结束时间}'
                            and status <> '换产'	
                        --  and status in ('完工','结束','开工')
                                and TankName not like '%主挤%'
                                and TankName not in ('主挤_ABR_S1','主挤_ABR_S2','主挤_ABR_S3') )  AS current_row
                        LEFT JOIN 
                            (SELECT 
                                status,
                                 vbillcode, 
				                         lineno,
                                 materialName, 
                                 quantity, 
                                 TankName, 
                                 bus_date, 
                                    Ratio,
                                 ROW_NUMBER() OVER (PARTITION BY TankName ORDER BY bus_date ) AS row_num
                             FROM {tableName} where vbillcode = '{vbillcode}'
                            and quantity+0 >0
                            and bus_date >= '{开始时间}' and bus_date <= '{结束时间}'
                            and status <> '换产'	
                        --  and STATUS in ('完工','结束','开工')
                                and TankName not like '%主挤%'
                                and TankName not in ('主挤_ABR_S1','主挤_ABR_S2','主挤_ABR_S3')) AS previous_row
                        ON current_row.TankName = previous_row.TankName 
                           AND current_row.row_num = previous_row.row_num + 1
                        ORDER BY current_row.TankName, current_row.bus_date DESC;

                            ";

            int r2 = SqlSugarHelp.scope.Ado.ExecuteCommand(sql);
            logger.Info("结束计算明细，计算结果行数：" + r2);
            return r2;
        }
        public int 按时间计算主挤明细(string vbillcode, string tableName, string 开始时间, string 结束时间)
        {
            logger.Info("开始计算明细");
            ////计算时先删除
            //string sqldelete = $@"
            //            DELETE FROM ncmaterialoutdetail WHERE vbillcode = '{vbillcode}';
            //            ";
            //int r = SqlSugarHelp.scope.Ado.ExecuteCommand(sqldelete);
            string sql = $@"
                        INSERT into ncmaterialoutdetail(guid,vbillcode,lineno,current_materialName,TankName,current_quantity,previous_quantity,quantity_diff,bus_date)
                            SELECT  UUID(),
                                current_row.vbillcode,
		                            current_row.lineno,
                                current_row.materialName AS current_materialName,
                                current_row.TankName,
                                ifnull(current_row.quantity, 0) +0 AS current_quantity,
                               ifnull(previous_row.quantity, 0) +0 AS previous_quantity,
	                             CASE 
                                    WHEN current_row.row_num = 1 THEN current_row.quantity
                                    ELSE ifnull((current_row.quantity - previous_row.quantity  ), 0)+0
                                END AS quantity_diff,
		
                                current_row.bus_date
                            FROM 
                                (SELECT 
                                     vbillcode, 
                                             lineno,
                                     materialName, 
                                     quantity, 
                                     TankName, 
                                     bus_date, 
                                    Ratio,
                                     ROW_NUMBER() OVER (PARTITION BY TankName ORDER BY bus_date ) AS row_num
                                 FROM {tableName} where vbillcode = '{vbillcode}' 
                                and quantity+0 >0
                                and bus_date >= '{开始时间}' and bus_date <= '{结束时间}'
                                and status <> '换产'	
                            --  and status in ('完工','结束','开工')
                                    and TankName    like '%主挤%'
                                    and TankName not in ('主挤_ABR_S1','主挤_ABR_S2','主挤_ABR_S3','主挤_D') 
			                            )  AS current_row
                            LEFT JOIN 
                                (SELECT 
                                     vbillcode, 
                                             lineno,
                                     materialName, 
                                     quantity, 
                                    TankName, 
                                     bus_date, 
                                        Ratio,
                                     ROW_NUMBER() OVER (PARTITION BY TankName ORDER BY bus_date ) AS row_num
                                 FROM {tableName} where vbillcode = '{vbillcode}'
                                 and quantity+0 >0
                                and bus_date >= '{开始时间}' and bus_date <= '{结束时间}'
                                and status <> '换产'	
                            --  and STATUS in ('完工','结束','开工')
                                    and TankName   like '%主挤%'
                                    and TankName not in ('主挤_ABR_S1','主挤_ABR_S2','主挤_ABR_S3','主挤_D')
				                            ) AS previous_row
                            ON current_row.TankName = previous_row.TankName 
                               AND current_row.row_num = previous_row.row_num + 1
                            ORDER BY current_row.TankName, current_materialName, current_row.bus_date DESC;
                        
                            ";

            int r2 = SqlSugarHelp.scope.Ado.ExecuteCommand(sql);
            logger.Info("结束计算明细，计算结果行数：" + r2);
            return r2;
        }

        public int 按时间计算ABR明细(string vbillcode, string tableName, string 开始时间, string 结束时间)
        {
            logger.Info("开始计算明细");

            string sql = $@"
                        INSERT into ncmaterialoutdetail(guid,vbillcode,lineno,current_materialName,TankName,current_quantity,previous_quantity,quantity_diff,bus_date)

                         SELECT  UUID(),
                            current_row.vbillcode,
                                current_row.lineno,
						                    CASE 
                        WHEN current_row.row_num = 1 THEN current_row.materialName 
		                     ELSE previous_row.materialName
		                     end 
                             AS current_materialName,
                            current_row.TankName,
                            ifnull(current_row.quantity, 0) +0 AS current_quantity,
                           ifnull(previous_row.quantity, 0) +0 AS previous_quantity,

                       CASE 
                        WHEN current_row.row_num = 1 THEN current_row.quantity 
                        ELSE (ifnull((current_row.quantity - previous_row.quantity ), 0)+0)
                    END * 
                    CASE 
                        WHEN current_row.row_num = 1 THEN current_row.Ratio 
                        ELSE previous_row.Ratio
                    END 
                    /100
                             AS quantity_diff,

                            current_row.bus_date
                        FROM 
                            (SELECT status,
                                 vbillcode, 
				                         lineno,
                                 materialName, 
                                 quantity, 
                                 TankName, 
                                 bus_date, 
                                 Ratio,
                                 ROW_NUMBER() OVER (PARTITION BY TankName ORDER BY bus_date ) AS row_num
                             FROM {tableName} where vbillcode = '{vbillcode}' 
                            and quantity+0 >0
                            and bus_date >= '{开始时间}' and bus_date <= '{结束时间}'
                            and status <> '换产'	
                            -- and status in ('完工','结束','开工')
                                and TankName  in ('主挤_ABR_S1','主挤_ABR_S2','主挤_ABR_S3') )  AS current_row
                        LEFT JOIN 
                            (SELECT status,
                                 vbillcode, 
				                         lineno,
                                 materialName, 
                                 quantity, 
                                 TankName, 
                                 bus_date, 
                                 Ratio,
                                 ROW_NUMBER() OVER (PARTITION BY TankName ORDER BY bus_date ) AS row_num
                             FROM {tableName} where vbillcode = '{vbillcode}'
                                and quantity+0 >0
                                and bus_date >= '{开始时间}' and bus_date <= '{结束时间}'
                                and status <> '换产'	
                            -- and STATUS in ('完工','结束','开工')
                                and TankName  in ('主挤_ABR_S1','主挤_ABR_S2','主挤_ABR_S3')) AS previous_row
                        ON current_row.TankName = previous_row.TankName 
                           AND current_row.row_num = previous_row.row_num + 1
                        ORDER BY current_row.TankName, current_row.bus_date DESC;

                            ";

            int r2 = SqlSugarHelp.scope.Ado.ExecuteCommand(sql);
            logger.Info("结束计算明细，计算结果行数：" + r2);


            return r2;
        }

        private int 按时间计算1号单辅挤明细(string vbillcode, string tableName, string 开始时间, string 结束时间)
        {
            logger.Info("开始计算明细");
            ////计算时先删除
            //string sqldelete = $@"
            //            DELETE FROM ncmaterialoutdetail WHERE vbillcode = '{vbillcode}';
            //            ";
            //int r = SqlSugarHelp.scope.Ado.ExecuteCommand(sqldelete);
            string sql = $@"
                        INSERT into ncmaterialoutdetail(guid,vbillcode,lineno,current_materialName,TankName,current_quantity,previous_quantity,quantity_diff,bus_date)
                             SELECT  UUID(),
                            '{vbillcode}',
                                current_row.lineno,
						                    CASE 
                        WHEN current_row.row_num = 1 THEN current_row.materialName 
		                     ELSE previous_row.materialName
		                     end 
                             AS current_materialName,
                            current_row.TankName,
                            ifnull(current_row.quantity, 0) +0 AS current_quantity,
                           ifnull(previous_row.quantity, 0) +0 AS previous_quantity,

                       CASE 
                        WHEN current_row.row_num = 1 THEN current_row.quantity 
                        ELSE (ifnull((current_row.quantity - previous_row.quantity ), 0)+0)
                    END * 
                    CASE 
                        WHEN current_row.row_num = 1 THEN current_row.Ratio 
                        ELSE previous_row.Ratio
                    END 
                    /100
                             AS quantity_diff,

                            current_row.bus_date
                        FROM 
                            (SELECT 
                                status,
                                 vbillcode, 
				                         lineno,
                                 materialName, 
                                 quantity, 
                                  TankName, 
                                 bus_date, 
                                Ratio,
                                 ROW_NUMBER() OVER (PARTITION BY TankName ORDER BY bus_date ) AS row_num
                             FROM {tableName} where vbillcode = '1' 
                            and quantity+0 >0
                            and bus_date >= '{开始时间}' and bus_date <= '{结束时间}'
                            and status <> '换产'	
                        --  and status in ('完工','结束','开工')
                                and TankName not like '%主挤%'
                                and TankName not in ('主挤_ABR_S1','主挤_ABR_S2','主挤_ABR_S3') )  AS current_row
                        LEFT JOIN 
                            (SELECT 
                                status,
                                 vbillcode, 
				                         lineno,
                                 materialName, 
                                 quantity, 
                                 TankName, 
                                 bus_date, 
                                    Ratio,
                                 ROW_NUMBER() OVER (PARTITION BY TankName ORDER BY bus_date ) AS row_num
                             FROM {tableName} where vbillcode = '1'
                            and quantity+0 >0
                            and bus_date >= '{开始时间}' and bus_date <= '{结束时间}'
                            and status <> '换产'	
                        --  and STATUS in ('完工','结束','开工')
                                and TankName not like '%主挤%'
                                and TankName not in ('主挤_ABR_S1','主挤_ABR_S2','主挤_ABR_S3')) AS previous_row
                        ON current_row.TankName = previous_row.TankName 
                           AND current_row.row_num = previous_row.row_num + 1
                        ORDER BY current_row.TankName, current_row.bus_date DESC;

                            ";

            int r2 = SqlSugarHelp.scope.Ado.ExecuteCommand(sql);
            logger.Info("结束计算明细，计算结果行数：" + r2);
            return r2;
        }
        private int 按时间计算1号单主挤明细(string vbillcode, string tableName, string 开始时间, string 结束时间)
        {
            logger.Info("开始计算明细");
            ////计算时先删除
            //string sqldelete = $@"
            //            DELETE FROM ncmaterialoutdetail WHERE vbillcode = '{vbillcode}';
            //            ";
            //int r = SqlSugarHelp.scope.Ado.ExecuteCommand(sqldelete);
            string sql = $@"
                        INSERT into ncmaterialoutdetail(guid,vbillcode,lineno,current_materialName,TankName,current_quantity,previous_quantity,quantity_diff,bus_date)
                            SELECT  UUID(),
                               '{vbillcode}',
		                            current_row.lineno,
                                current_row.materialName AS current_materialName,
                                current_row.TankName,
                                ifnull(current_row.quantity, 0) +0 AS current_quantity,
                               ifnull(previous_row.quantity, 0) +0 AS previous_quantity,
	                             CASE 
                                    WHEN current_row.row_num = 1 THEN current_row.quantity
                                    ELSE ifnull((current_row.quantity - previous_row.quantity  ), 0)+0
                                END AS quantity_diff,
		
                                current_row.bus_date
                            FROM 
                                (SELECT 
                                     vbillcode, 
                                             lineno,
                                     materialName, 
                                     quantity, 
                                     TankName, 
                                     bus_date, 
                                    Ratio,
                                     ROW_NUMBER() OVER (PARTITION BY TankName ORDER BY bus_date ) AS row_num
                                 FROM {tableName} where vbillcode = '1' 
                                and quantity+0 >0
                                and bus_date >= '{开始时间}' and bus_date <= '{结束时间}'
                                and status <> '换产'	
                            --  and status in ('完工','结束','开工')
                                    and TankName    like '%主挤%'
                                    and TankName not in ('主挤_ABR_S1','主挤_ABR_S2','主挤_ABR_S3','主挤_D') 
			                            )  AS current_row
                            LEFT JOIN 
                                (SELECT 
                                     vbillcode, 
                                             lineno,
                                     materialName, 
                                     quantity, 
                                    TankName, 
                                     bus_date, 
                                        Ratio,
                                     ROW_NUMBER() OVER (PARTITION BY TankName ORDER BY bus_date ) AS row_num
                                 FROM {tableName} where vbillcode = '1'
                                 and quantity+0 >0
                                and bus_date >= '{开始时间}' and bus_date <= '{结束时间}'
                                and status <> '换产'	
                            --  and STATUS in ('完工','结束','开工')
                                    and TankName   like '%主挤%'
                                    and TankName not in ('主挤_ABR_S1','主挤_ABR_S2','主挤_ABR_S3','主挤_D')
				                            ) AS previous_row
                            ON current_row.TankName = previous_row.TankName 
                               AND current_row.row_num = previous_row.row_num + 1
                            ORDER BY current_row.TankName, current_materialName, current_row.bus_date DESC;
                        
                            ";

            int r2 = SqlSugarHelp.scope.Ado.ExecuteCommand(sql);
            logger.Info("结束计算明细，计算结果行数：" + r2);
            return r2;
        }

        private int 按时间计算1号单ABR明细(string vbillcode, string tableName, string 开始时间, string 结束时间)
        {
            logger.Info("开始计算明细");

            string sql = $@"
                        INSERT into ncmaterialoutdetail(guid,vbillcode,lineno,current_materialName,TankName,current_quantity,previous_quantity,quantity_diff,bus_date)

                         SELECT  UUID(),
                            '{vbillcode}',
                                current_row.lineno,
						                    CASE 
                        WHEN current_row.row_num = 1 THEN current_row.materialName 
		                     ELSE previous_row.materialName
		                     end 
                             AS current_materialName,
                            current_row.TankName,
                            ifnull(current_row.quantity, 0) +0 AS current_quantity,
                           ifnull(previous_row.quantity, 0) +0 AS previous_quantity,

                       CASE 
                        WHEN current_row.row_num = 1 THEN current_row.quantity 
                        ELSE (ifnull((current_row.quantity - previous_row.quantity ), 0)+0)
                    END * 
                    CASE 
                        WHEN current_row.row_num = 1 THEN current_row.Ratio 
                        ELSE previous_row.Ratio
                    END 
                    /100
                             AS quantity_diff,

                            current_row.bus_date
                        FROM 
                            (SELECT status,
                                 vbillcode, 
				                         lineno,
                                 materialName, 
                                 quantity, 
                                 TankName, 
                                 bus_date, 
                                 Ratio,
                                 ROW_NUMBER() OVER (PARTITION BY TankName ORDER BY bus_date ) AS row_num
                             FROM {tableName} where vbillcode = '1' 
                            and quantity+0 >0
                            and bus_date >= '{开始时间}' and bus_date <= '{结束时间}'
                            and status <> '换产'	
                            -- and status in ('完工','结束','开工')
                                and TankName  in ('主挤_ABR_S1','主挤_ABR_S2','主挤_ABR_S3') )  AS current_row
                        LEFT JOIN 
                            (SELECT status,
                                 vbillcode, 
				                         lineno,
                                 materialName, 
                                 quantity, 
                                 TankName, 
                                 bus_date, 
                                 Ratio,
                                 ROW_NUMBER() OVER (PARTITION BY TankName ORDER BY bus_date ) AS row_num
                             FROM {tableName} where vbillcode = '1'
                                and quantity+0 >0
                                and bus_date >= '{开始时间}' and bus_date <= '{结束时间}'
                                and status <> '换产'	
                            -- and STATUS in ('完工','结束','开工')
                                and TankName  in ('主挤_ABR_S1','主挤_ABR_S2','主挤_ABR_S3')) AS previous_row
                        ON current_row.TankName = previous_row.TankName 
                           AND current_row.row_num = previous_row.row_num + 1
                        ORDER BY current_row.TankName, current_row.bus_date DESC;

                            ";

            int r2 = SqlSugarHelp.scope.Ado.ExecuteCommand(sql);
            logger.Info("结束计算明细，计算结果行数：" + r2);


            return r2;
        }

        private int 汇总到出库表(string vbillcode)
        {
            logger.Info("开始汇总到出库表");
            string sqldelete = $@"
                            DELETE FROM ncmaterialout WHERE vbillcode = '{vbillcode}' and resultcode <> '1';
                            ";
            int r = SqlSugarHelp.scope.Ado.ExecuteCommand(sqldelete);
            //更新造粒料
            string sqlupdate = $@"update ncmaterialoutdetail set current_materialName = 'SP' where current_materialName in ('造粒料','ZL')";
            int r3 = SqlSugarHelp.scope.Ado.ExecuteCommand(sqlupdate);
            //去掉括号
            List<ncmaterialoutdetail> ncmaterialoutdetails = SqlSugarHelp.scope.Queryable<ncmaterialoutdetail>().Where(it => it.vbillcode == vbillcode).ToList();
            if (ncmaterialoutdetails != null && ncmaterialoutdetails.Count > 0)
            {
                string pattern = @"（.*?）|\(.*?\)"; // 匹配中英文括号及其内容

                foreach (var item in ncmaterialoutdetails)
                {
                    string plc物料规格 = "";
                    if (string.IsNullOrWhiteSpace(item.current_materialName) == false)
                    {
                        plc物料规格 = Regex.Replace(item.current_materialName, pattern, string.Empty).ToUpper();
                    }
                    else
                    {
                        logger.Error($"{vbillcode}有物料名称为空白");
                        textBoxMemo.Text = $"{vbillcode}有物料名称为空白";
                        return 1;
                    }
                    
                    item.current_materialName = plc物料规格;

                }

                int r4 = SqlSugarHelp.scope.Updateable(ncmaterialoutdetails).ExecuteCommand();
            }


            

            string sql = $@"
                            insert into ncmaterialout (guid,vbillcode,lineno,spec,PLCQuantity,resultcode)
                            select UUID(),REPLACE(REPLACE(vbillcode, CHAR(10), ''), CHAR(13), '') vbillcode, REPLACE(REPLACE(lineno, CHAR(10), ''), CHAR(13), '') lineno,current_materialName,sum(quantity_diff),0
                            from ncmaterialoutdetail 
                            where quantity_diff > 0 and vbillcode = '{vbillcode}'
                            GROUP BY REPLACE(REPLACE(vbillcode, CHAR(10), ''), CHAR(13), '') , REPLACE(REPLACE(lineno, CHAR(10), ''), CHAR(13), '') ,current_materialName
                            ";
            int r2 = SqlSugarHelp.scope.Ado.ExecuteCommand(sql);
            logger.Info("2.汇总到出库表，计算结果行数：" + r2);



            return r2;
        }

        private int 查NC现存量并更新(string vbillcode, string lineno)
        {
            string cwarehouseid = "";
            if (lineno == "1")
            {
                cwarehouseid = ConfigurationManager.AppSettings["BOPP一线原料线边仓"].ToString();
            }
            else
            {
                cwarehouseid = ConfigurationManager.AppSettings["BOPP二线原料线边仓"].ToString();
            }

            

       

            List<ncmaterialout> ncmaterialouts = SqlSugarHelp.scope.Queryable<ncmaterialout>().Where(it => it.vbillcode == vbillcode).ToList();
            List<ncmaterialout> 同物料多批次 = new List<ncmaterialout>();

            if (ncmaterialouts != null && ncmaterialouts.Count > 0)
            {
                string sqlNC = $@"
                            select  bd_material.PK_MATERIAL,bd_material.materialtype 物料型号,bd_material.code 物料编号,
                        bd_material.name 物料名称,bd_material.materialspec 物料规格,ic_onhanddim.vbatchcode 批号,ic_onhandnum.nonhandnum 可用量,
                        scm_batchcode.dinbounddate 入库日期
                        from ic_onhandnum
                        left join ic_onhanddim on ic_onhandnum.pk_onhanddim =  ic_onhanddim.pk_onhanddim
                        left join  bd_material on bd_material.pk_material = ic_onhanddim.cmaterialvid
                        left join scm_batchcode on scm_batchcode.pk_batchcode = ic_onhanddim.pk_batchcode
                        left join ic_storestate on ic_storestate.pk_storestate = ic_onhanddim.cstateid
                        left join bd_stordoc on bd_stordoc.pk_stordoc = ic_onhanddim.cwarehouseid
                        where 1=1 
                        and ic_onhanddim.pk_org = '0001A11000000000Q5NJ'
                        and ic_onhanddim.cwarehouseid = '{cwarehouseid}'
                        and ic_onhandnum.nonhandnum > 0
                        order by  bd_material.PK_MATERIAL,scm_batchcode.dinbounddate ";
                List<NC线边仓现存量> nC线边仓现存量s = OraSqlSugarHelp.scope.Ado.SqlQuery<NC线边仓现存量>(sqlNC);

                foreach (var item in ncmaterialouts)
                {
                    string pattern = @"（.*?）|\(.*?\)"; // 匹配中英文括号及其内容
                    string plc物料规格 = Regex.Replace(item.spec, pattern, string.Empty);
                    item.spec = plc物料规格;
                    //根据 item的spec，查询nC线边仓现存量s对应物料规格的PK_MATERIAL
                    var nC线边仓现存量 = nC线边仓现存量s.Where(it => it.物料规格.Contains(item.spec)).ToList();
                    if (nC线边仓现存量 != null && nC线边仓现存量.Count > 0)
                    {
                        item.pk_material = nC线边仓现存量[0].PK_MATERIAL;
                        item.materialName = nC线边仓现存量[0].物料名称;
                        item.materialCode = nC线边仓现存量[0].物料编号;
                        List<(string 批次, decimal 数量, decimal nc可用量)> 匹配到批次用量 = FindBatches(nC线边仓现存量, item.PLCQuantity.GetValueOrDefault());
                        //如果大于1，就要往ncmaterialout塞额外的几行
                        if (匹配到批次用量 != null && 匹配到批次用量.Count > 0)
                        {
                            for (int i = 0; i < 匹配到批次用量.Count; i++)
                            {
                                if (i == 0)
                                {
                                    item.batch_num = 匹配到批次用量[0].批次;
                                    item.ActualQuantity = 匹配到批次用量[0].数量;
                                    item.NCStockQuantity = 匹配到批次用量[0].nc可用量;
                                }
                                else
                                {
                                    ncmaterialout nc = item.Adapt<ncmaterialout>();
                                    nc.guid = Guid.NewGuid().ToString();
                                    nc.batch_num = 匹配到批次用量[i].批次;
                                    nc.ActualQuantity = 匹配到批次用量[i].数量;
                                    nc.NCStockQuantity = 匹配到批次用量[i].nc可用量;
                                    同物料多批次.Add(nc);
                                }
                            }
                        }
                        //if (匹配到批次用量.Count == 1)
                        //{
                        //    item.batch_num = 匹配到批次用量[0].批次;
                        //    item.ActualQuantity = 匹配到批次用量[0].数量;
                        //    item.NCStockQuantity = 匹配到批次用量[0].nc可用量;
                        //}
                    }
                    else
                    {
                        item.ActualQuantity = 0;
                        item.NCStockCheck = "线边仓没有该物料可用量";
                        textBoxMemo.Text = $"线边仓没有该物料{item.spec}可用量";
                    }
                }
                //更新
                ncmaterialouts.AddRange(同物料多批次);
                int r = SqlSugarHelp.scope.Storageable(ncmaterialouts).WhereColumns(it => it.guid).ExecuteCommand();
                //插入

                return r;
            }
            return 0;
        }

        public static List<(string 批次, decimal 数量, decimal nc可用量)> FindBatches(List<NC线边仓现存量> 可用量List, decimal 需要用量)
        {
            List<(string 批次, decimal 数量, decimal nc可用量)> result = new List<(string 批次, decimal 数量, decimal nc可用量)>();
            decimal remaining = 需要用量;

            foreach (var item in 可用量List)
            {
                if (remaining <= 0) break;

                decimal useAmount = Math.Min(item.可用量, remaining);
                result.Add((item.批号, useAmount, item.可用量));
                remaining -= useAmount;
            }

            return result;
        }
        #region 拉伸报表
        private void button拉伸报表测试_Click(object sender, EventArgs e)
        {
            查投产工单();
            更新工单状态();
            //查投产的工单
            查NC工单();
            计算工单用料产量();
            //查mes称重记录
            查MES称重记录();

        }

        private void 查MES称重记录()
        {
            string url = ConfigurationManager.AppSettings["mesweighturl"].ToString();
            //查出所有的工单号
            List<VIEW_拉伸报表> view_拉伸报表list = SQLSERVERSqlSugarHelp.scope.Queryable<VIEW_拉伸报表>().OrderBy(it => it.订单日期, OrderByType.Desc).Take(40).ToList();

            if (view_拉伸报表list != null && view_拉伸报表list.Count > 0)
            {
                int r0 = SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand("truncate table [dbo].[VIEW_MES拉伸称重]");
                List<string> 工单号s = view_拉伸报表list.Select(it => it.工单号).ToList();
                Dictionary<string, List<string>> dic = new Dictionary<string, List<string>>();
                dic.Add("codes", 工单号s);

                HttpData httpData = HttpHelper.HttpPost<HttpData>(url, JsonConvert.SerializeObject(dic));
                List<VIEW_MES拉伸称重> view_MES拉伸称重List = httpData.data;
                //塞入数据库
                if (view_MES拉伸称重List != null && view_MES拉伸称重List.Count > 0)
                {
                    view_MES拉伸称重List.ForEach(it =>
                    {
                        it.guid = Guid.NewGuid();
                    });
                    var r = SQLSERVERSqlSugarHelp.scope.Storageable(view_MES拉伸称重List).WhereColumns(it => it.vbillcode).ExecuteCommand();
                }

                //按工单号计算成品重量
                foreach (var item in view_拉伸报表list)
                {
                    List<VIEW_MES拉伸称重> viewMES工单正品称重 = SQLSERVERSqlSugarHelp.scope.Queryable<VIEW_MES拉伸称重>().Where(it => it.vbillcode == item.工单号 && it.status != "-1").ToList();
                    List<string> guids = viewMES工单正品称重.Select(it => it.plc_guid).ToList();
                    List<wgrk> scllList = new List<wgrk>();
                    string sql = "";
                    if (item.产线 == "1")
                    {
                        sql = $@"select * from wgrk where vbillcode = '{item.工单号}' and status = '完工' and quantity > 1000 
                                                            and NOT EXISTS (SELECT 1 FROM wgrk_weighted WHERE wgrk_weighted.guid  = wgrk.guid) 
                                                            and NOT EXISTS (SELECT 1 FROM wgrk_film WHERE wgrk_film.guid  = wgrk.guid) 
                                                            and guid not in ('{string.Join("','", guids)}')";
                    }
                    if (item.产线 == "2")
                    {
                        sql = $@"select * from wgrk二线 where vbillcode = '{item.工单号}' and  status = '完工' and quantity > 1000 
                                                            and NOT EXISTS (SELECT 1 FROM wgrk二线_weighted WHERE wgrk二线_weighted.guid  = wgrk二线.guid)
                                                            and NOT EXISTS (SELECT 1 FROM wgrk_film WHERE wgrk_film.guid  = wgrk二线.guid)
                                                            and guid not in ('{string.Join("','", guids)}')";
                    }
                    scllList = SqlSugarHelp.scope.Ado.SqlQuery<wgrk>(sql);
                    //待称重重量
                    decimal 待称重重量 = scllList.Where(it => string.IsNullOrWhiteSpace(it.quantity) == false).Sum(it => decimal.Parse(it.quantity));
                    decimal 正品重量 = viewMES工单正品称重.Sum(it => it.original_quantity).GetValueOrDefault();
                    if (item.工单状态 == "结束")
                    {
                        item.MES母卷入库数量 = 正品重量;
                    }
                    else
                    {
                        item.MES母卷入库数量 = 正品重量 + 待称重重量;
                    }

                    item.副产品合计 = item.投入原料实时合计数量 - item.MES母卷入库数量;
                    if (item.投入原料实时合计数量.GetValueOrDefault() > 0)
                    {
                        item.拉伸成品率 = item.MES母卷入库数量 / item.投入原料实时合计数量;
                    }
                    
                    if (item.总工时 > 0)
                    {
                        item.小时产量 = item.MES母卷入库数量 / item.总工时;
                    }

                }
                //更新
                var r2 = SQLSERVERSqlSugarHelp.scope.Updateable(view_拉伸报表list).ExecuteCommand();
            }


        }

        private void 计算工单用料产量()
        {
            List<VIEW_拉伸报表> list = SQLSERVERSqlSugarHelp.scope.Queryable<VIEW_拉伸报表>().OrderBy(it =>it.订单日期,OrderByType.Desc).Take(40).ToList();
            if (list != null && list.Count > 0)
            {
                foreach (var item in list)
                {
                    List<scll> scllList = new List<scll>();
                    if (item.产线 == "1")
                    {
                        scllList = SqlSugarHelp.scope.Queryable<scll>().AS("scll")
                                                        .Where(it => it.vbillcode == item.工单号).OrderBy(it => it.bus_date).ToList();

                    }
                    if (item.产线 == "2")
                    {
                        scllList = SqlSugarHelp.scope.Queryable<scll>().AS("scll二线")
                                                        .Where(it => it.vbillcode == item.工单号).OrderBy(it => it.bus_date).ToList();
                    }

                    if (scllList != null && scllList.Count > 0)
                    {
                        if (item.工单状态 == "开工" || item.工单状态 == "停机")
                        {
                            SqlSugarHelp.scope.Ado.ExecuteCommand($"delete from ncmaterialoutdetail where vbillcode = '{item.工单号}'");
                            string tableName = "";
                            if (item.产线 == "1")
                            {
                                tableName = "scll";
                            }
                            else
                            {
                                tableName = "scll二线";
                            }

                            计算辅挤明细(item.工单号, tableName);
                            计算ABR明细(item.工单号, tableName);
                            计算主挤明细(item.工单号, tableName);


                            var 开工 = scllList.Where(it => it.status == "开工").OrderBy(it => it.bus_date).FirstOrDefault();
                            item.工单开始时间 = 开工?.bus_date;
                            string sql2 = $@"SELECT  sum(quantity_diff) 投入原料合计 FROM `plc`.`ncmaterialoutdetail` where vbillcode = '{item.工单号}' and quantity_diff > 0";
                            decimal? 投入原料合计2 = SqlSugarHelp.scope.Ado.GetDecimal(sql2);
                            item.投入原料实时合计数量 = 投入原料合计2;
                        }

                        if (item.工单状态 == "结束")
                        {
                            var 开工 = scllList.Where(it => it.status == "开工").OrderBy(it => it.bus_date).FirstOrDefault();
                            var 结束 = scllList.Where(it => it.status == "结束").OrderByDescending(it => it.bus_date).FirstOrDefault();
                            if (开工 != null)
                            {
                                item.工单开始时间 = 开工?.bus_date;
                            }
                            else
                            {
                                item.工单开始时间 = scllList.Min(it => it.bus_date);
                            }
                            if (结束 != null)
                            {
                                item.工单结束时间 = 结束?.bus_date;
                            }
                            else
                            {
                                item.工单结束时间 = scllList.Max(it => it.bus_date);
                            }


                            if (item.工单结束时间 != null)
                            {
                                item.总工时 = (decimal?)(item.工单结束时间 - item.工单开始时间).Value.TotalHours;
                            }

                            decimal? 投入原料合计 = 0;
                            if (SqlSugarHelp.scope.Queryable<ncmaterialout>().Where(it => it.vbillcode == item.工单号).Any())
                            {
                                //查计算后的表
                                string sql = $@"SELECT  sum(quantity_diff) 投入原料合计 FROM `plc`.`ncmaterialoutdetail` where vbillcode = '{item.工单号}'";
                                投入原料合计 = SqlSugarHelp.scope.Ado.GetDecimal(sql);
                            }
                            if (投入原料合计.GetValueOrDefault() > 0)
                            {
                                item.投入原料实时合计数量 = 投入原料合计;
                            }
                            else
                            {
                                string tableName = "";
                                if (item.产线 == "1")
                                {
                                    tableName = "scll";
                                }
                                else
                                {
                                    tableName = "scll二线";
                                }
                                SqlSugarHelp.scope.Ado.ExecuteCommand($"delete from ncmaterialoutdetail where vbillcode = '{item.工单号}'");

                                计算辅挤明细(item.工单号, tableName);
                                计算ABR明细(item.工单号, tableName);
                                计算主挤明细(item.工单号, tableName);
                                string sql2 = $@"SELECT  sum(quantity_diff) 投入原料合计 FROM `plc`.`ncmaterialoutdetail` where vbillcode = '{item.工单号}' and quantity_diff > 0";
                                decimal? 投入原料合计2 = SqlSugarHelp.scope.Ado.GetDecimal(sql2);
                                item.投入原料实时合计数量 = 投入原料合计2;
                                //item.投入原料实时合计数量 = scllList.Where(it => string.IsNullOrWhiteSpace(it.quantity) == false).Max(it => decimal.Parse(it.quantity));
                            }

                        }

                        ////找出 状态是开工的 bus_date最小的时间
                        //var 开工 = scllList.Where(it => it.status == "开工").OrderBy(it => it.bus_date).FirstOrDefault();
                        ////找出 状态是结束的 bus_date最大的时间
                        //var 结束 = scllList.Where(it => it.status == "结束").OrderByDescending(it => it.bus_date).FirstOrDefault();
                        //if (开工 != null)
                        //{
                        //    //item.工单状态 = "开工";
                        //    item.工单开始时间 = 开工.bus_date;
                        //}
                        //if (结束 != null)
                        //{
                        //    //item.工单状态 = "结束";
                        //    item.工单结束时间 = 结束.bus_date;
                        //}
                        //else
                        //{
                        //    item.工单结束时间 = scllList.Max(it => it.bus_date);
                        //    item.投入原料实时合计数量 = scllList.Where(it => string.IsNullOrWhiteSpace(it.quantity) == false).Select(it => decimal.Parse(it.quantity)).Max();
                        //    //检查后面的时间有没有开工的单子
                        //    if (item.产线 == "1")
                        //    {
                        //        var a = SqlSugarHelp.scope.Queryable<scll>().Where(it => it.vbillcode != item.工单号 && it.bus_date > item.工单结束时间).ToList();
                        //        if (a != null && a.Count > 0)
                        //        {
                        //            //item.工单状态 = "结束";
                        //        }
                        //    }
                        //    else
                        //    {
                        //        var a = SqlSugarHelp.scope.Queryable<scll二线>().Where(it => it.vbillcode != item.工单号 && it.bus_date > item.工单结束时间).ToList();
                        //        if (a != null && a.Count > 0)
                        //        {
                        //            //item.工单状态 = "结束";
                        //        }
                        //    }


                        //}
                        ////根据开始 结束 时间计算小时差
                        //if (item.工单开始时间 != null && item.工单结束时间 != null)
                        //{
                        //    item.总工时 = (decimal?)(item.工单结束时间 - item.工单开始时间).Value.TotalHours;
                        //}

                        //if (item.工单状态 == "结束")
                        //{
                        //    //查计算后的表
                        //    string sql = $@"SELECT  sum(quantity_diff) 投入原料合计 FROM `plc`.`ncmaterialoutdetail` where vbillcode = '{item.工单号}'";
                        //    decimal? 投入原料合计 = SqlSugarHelp.scope.Ado.GetDecimal(sql);
                        //    if (投入原料合计.GetValueOrDefault() > 0)
                        //    {
                        //        item.投入原料实时合计数量 = 投入原料合计;
                        //    }
                        //    else
                        //    {
                        //        item.投入原料实时合计数量 = scllList.Where(it => string.IsNullOrWhiteSpace(it.quantity) == false).Max(it => decimal.Parse(it.quantity));
                        //    }
                        //}
                    }
                    else
                    {
                        //item.工单状态 = "结束";
                    }

                }
                //更新
                var r = SQLSERVERSqlSugarHelp.scope.Updateable(list).ExecuteCommand();

            }



        }

        private void 查NC工单()
        {
            List<VIEW_拉伸报表> list = SQLSERVERSqlSugarHelp.scope.Queryable<VIEW_拉伸报表>().Where(it => string.IsNullOrEmpty(it.产品型号)).ToList();
            //拼接in查询
            if (list != null && list.Count > 0)
            {
                string inStr = string.Join("','", list.Select(it => it.工单号).ToList());
                string sql = $@"select mm_pmo.dbilldate 订单日期,mm_pmo.vbillcode 工单号,
                            CASE
                            WHEN mm_mo.cdeptid LIKE '1001A110000000V585T3' THEN '1'
                            WHEN mm_mo.cdeptid LIKE '1001A110000000V58DLM' THEN '2'
                            ELSE mm_mo.cdeptid
                            END AS 产线, materialtype AS 产品型号
                            from mm_pmo  join mm_mo on  mm_mo.cpmohid = mm_pmo.CPMOHID 
                            left join bd_material on bd_material.pk_material = mm_mo.cmaterialid
                            where mm_mo.dr=0  and mm_pmo.pk_org = '0001A11000000000Q5NJ'
                            and mm_pmo.dbilldate > '2024-09-31' and mm_pmo.vbillcode in ('{inStr}')";
                List<VIEW_拉伸报表> vIEW_s = OraSqlSugarHelp.scope.Ado.SqlQuery<VIEW_拉伸报表>(sql);
                if (vIEW_s != null && vIEW_s.Count > 0)
                {
                    //插入VIEW_拉伸报表，如果工单号存在就不插入
                    var storage = SQLSERVERSqlSugarHelp.scope.Storageable(vIEW_s)
                                          .WhereColumns(it => it.工单号)//指定一个条件，当然支持多个 new {it.id,it.name}
                                          .ToStorage();//将数据进行分组
                    int r = storage.AsUpdateable.UpdateColumns(it => new { it.订单日期, it.产品型号 }).ExecuteCommand();

                }
            }
            SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand("delete from VIEW_拉伸报表 where 订单日期  < '2024-10-01' or 订单日期 is null");





        }

        private int 查投产工单()
        {

            string sql = @" select distinct vbillcode,lineno from (
                        select vbillcode,lineno from mm_pmo where creationtime > '2024-09-30 00:00:00'
                        union all
                        select vbillcode,lineno from mm_pmo_his where creationtime > '2024-09-30 00:00:00'
                        union all
                        select vbillcode,lineno from mm_pmo二线 where creationtime > '2024-09-30 00:00:00'
                        union all
                        select vbillcode,lineno from mm_pmo_his二线 where creationtime > '2024-09-30 00:00:00') t";

            List<mm_pmo> vIEW_s = SqlSugarHelp.scope.Ado.SqlQuery<mm_pmo>(sql);
            List<VIEW_拉伸报表> view_拉伸报表List = new List<VIEW_拉伸报表>();
            if (vIEW_s != null && vIEW_s.Count > 0)
            {
                foreach (var item in vIEW_s)
                {
                    VIEW_拉伸报表 vIEW_ = new VIEW_拉伸报表();
                    vIEW_.guid = Guid.NewGuid();
                    vIEW_.工单号 = item.vbillcode;
                    vIEW_.产线 = item.lineno;
                    vIEW_.工单状态 = "结束";
                    view_拉伸报表List.Add(vIEW_);
                }

                //插入VIEW_拉伸报表，如果工单号存在就不插入
                var storage = SQLSERVERSqlSugarHelp.scope.Storageable(view_拉伸报表List)
                                      .WhereColumns(it => it.工单号)//指定一个条件，当然支持多个 new {it.id,it.name}
                                      .ToStorage();//将数据进行分组
                int r = storage.AsInsertable.ExecuteCommand();

            }

            //更新工单状态


            // //先全部变为结束
            // string sqlR2 = "update VIEW_拉伸报表 set 工单状态 = '结束'";
            // SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand(sqlR2);
            // //查一线的投产
            // mm_pmo m1 = SqlSugarHelp.scope.Queryable<mm_pmo>().First();
            // //根据工单号到scll查上一个工单的最后一行状态
            // string sql上一工单状态 = $@"select * from scll where vbillcode = '{m1?.vbillcode}' order by bus_date desc limit 1";
            // scll scll = SqlSugarHelp.scope.Ado.SqlQuery<scll>(sql上一工单状态).FirstOrDefault();
            // //如果是停机
            // if (scll == null)
            // {
            //     sqlR2 = $"update VIEW_拉伸报表 set 工单状态 = '未开工' where 工单号 = '{m1?.vbillcode}';";
            //     int r2 = SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand(sqlR2);
            // }
            //else if (scll.status == "停机")
            // {
            //     //查自己最后的状态是不是开机
            //     string sql自己状态 = $@"select * from scll where vbillcode = '{m1?.vbillcode}' and status not like '更改配方%' order by bus_date desc limit 1";
            //     scll scll自己 = SqlSugarHelp.scope.Ado.SqlQuery<scll>(sql自己状态).FirstOrDefault();
            //     if (scll自己?.status == "换产" || scll自己?.status == "开工" || scll自己?.status == "停机")
            //     {
            //         //如果是开工，就是开工
            //         //m1.status = "停机";
            //         sqlR2 = $"update VIEW_拉伸报表 set 工单状态 = '停机' where 工单号 = '{m1?.vbillcode}';";
            //         int r2 = SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand(sqlR2);
            //     }
            //     else
            //     {
            //         sqlR2 = $"update VIEW_拉伸报表 set 工单状态 = '开工' where 工单号 = '{m1?.vbillcode}';";
            //         int r2 = SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand(sqlR2);
            //     }
            // }
            // else
            // {
            //     sqlR2 = $"update VIEW_拉伸报表 set 工单状态 = '开工' where 工单号 = '{m1?.vbillcode}';";
            //     int r2 = SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand(sqlR2);
            // }

            // //查二线的投产
            // mm_pmo二线 m2 = SqlSugarHelp.scope.Queryable<mm_pmo二线>().First();

            // //根据工单号到scll查上一个工单的最后一行状态
            // string sql二线上一工单状态 = $@"select * from scll二线 where vbillcode = '{m2?.vbillcode}' and status not like '更改配方%' order by bus_date desc limit 1";
            // scll二线 scll二线 = SqlSugarHelp.scope.Ado.SqlQuery<scll二线>(sql上一工单状态).FirstOrDefault();
            // //如果是停机
            // if (scll == null)
            // {
            //     sqlR2 = $"update VIEW_拉伸报表 set 工单状态 = '未开工' where 工单号 = '{m1?.vbillcode}';";
            //     int r2 = SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand(sqlR2);
            // }
            // else
            // if (scll二线.status == "停机")
            // {
            //     //查自己最后的状态是不是开机
            //     string sql自己状态 = $@"select * from scll二线 where vbillcode = '{m2?.vbillcode}' order by bus_date desc limit 1";
            //     scll二线 scll二线自己 = SqlSugarHelp.scope.Ado.SqlQuery<scll二线>(sql自己状态).FirstOrDefault();
            //     if (scll二线自己?.status == "换产" || scll二线自己?.status == "开工" || scll二线自己?.status == "停机")
            //     {
            //         //如果是开工，就是开工
            //         //m1.status = "停机";
            //         sqlR2 = $"update VIEW_拉伸报表 set 工单状态 = '停机' where 工单号 = '{m2?.vbillcode}';";
            //         int r2 = SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand(sqlR2);
            //     }
            //     else
            //     {
            //         sqlR2 += $"update VIEW_拉伸报表 set 工单状态 = '开工' where 工单号 = '{m2?.vbillcode}';";
            //         int r2 = SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand(sqlR2);
            //     }
            // }
            // else
            // {
            //     sqlR2 += $"update VIEW_拉伸报表 set 工单状态 = '开工' where 工单号 = '{m2?.vbillcode}';";
            //     int r2 = SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand(sqlR2);
            // }


            return 1;
        }

        public void 更新工单状态()
        {
            List<VIEW_拉伸报表> view = SQLSERVERSqlSugarHelp.scope.Ado.SqlQuery<VIEW_拉伸报表>("select * from VIEW_拉伸报表 where datediff(MONTH,订单日期,getdate())= 0 or 工单状态 <> '结束'");
            if (view != null && view.Count > 0)
            {
                foreach (var item in view)
                {
                    if (item.产线 == "1")
                    {
                        string sql自己状态 = $@"select * from scll where vbillcode = '{item.工单号}' and status not like '更改配方%' order by bus_date desc limit 1";
                        scll scll自己 = SqlSugarHelp.scope.Ado.SqlQuery<scll>(sql自己状态).FirstOrDefault();

                        if (SqlSugarHelp.scope.Queryable<scll>().Where(it => it.vbillcode == item.工单号).Any() == false) //没有材料出库单了 就是未开工
                        {
                            string sqlR2 = $"update VIEW_拉伸报表 set 工单状态 = '未开工' where 工单号 = '{item.工单号}';";
                            int r2 = SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand(sqlR2);
                        }
                        else if (SqlSugarHelp.scope.Queryable<scll>().Where(it => it.vbillcode != item.工单号 && it.bus_date > scll自己.bus_date).Any() == true) //有比自己大的材料出库单了 就是结束
                        {
                            string sqlR2 = $"update VIEW_拉伸报表 set 工单状态 = '结束' where 工单号 = '{item.工单号}';";
                            int r2 = SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand(sqlR2);
                        }
                        else
                        {
                            //查上一个工单有没有停机状态
                            string sql上一工单 = $@"select * from scll where vbillcode <> '{item.工单号}'  and bus_date < '{scll自己.bus_date}' order by bus_date desc limit 1";
                            scll scll上一工单 = SqlSugarHelp.scope.Ado.SqlQuery<scll>(sql上一工单).FirstOrDefault();
                            //查上一单有没有停机状态
                            string sql上一工单停机 = $@"select * from scll where vbillcode = '{scll上一工单.vbillcode}' and status = '停机' order by bus_date desc limit 1";
                            scll scll上一工单停机 = SqlSugarHelp.scope.Ado.SqlQuery<scll>(sql上一工单停机).FirstOrDefault();
                            if (scll上一工单停机 != null)
                            {
                                //查这单有没有传开机
                                string sql上一工单开机 = $@"select * from scll where vbillcode = '{scll上一工单.vbillcode}' and status = '开机' and bus_date > '{scll上一工单停机.bus_date}' order by bus_date desc limit 1";
                                scll scll上一工单开机 = SqlSugarHelp.scope.Ado.SqlQuery<scll>(sql上一工单开机).FirstOrDefault();
                                if (scll上一工单开机 != null)
                                {
                                    //如果有开机，就是开工
                                    //m1.status = "停机";
                                    string sqlR2 = $"update VIEW_拉伸报表 set 工单状态 = '开工' where 工单号 = '{item.工单号}';";
                                    int r2 = SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand(sqlR2);
                                }
                                else
                                {
                                    //查自己有没有开机
                                    string sql自己开机 = $@"select * from scll where vbillcode = '{item.工单号}' and status = '开机' order by bus_date desc limit 1";
                                    scll scll自己开机 = SqlSugarHelp.scope.Ado.SqlQuery<scll>(sql自己开机).FirstOrDefault();
                                    if (scll自己开机 != null)
                                    {
                                        //如果没有开机，就是停机
                                        //m1.status = "停机";
                                        string sqlR2 = $"update VIEW_拉伸报表 set 工单状态 = '开工' where 工单号 = '{item.工单号}';";
                                        int r2 = SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand(sqlR2);
                                    }
                                    else
                                    {
                                        //如果没有开机，就是停机
                                        //m1.status = "停机";
                                        string sqlR2 = $"update VIEW_拉伸报表 set 工单状态 = '停机' where 工单号 = '{item.工单号}';";
                                        int r2 = SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand(sqlR2);
                                    }

                                }
                            }
                            else
                            {
                                //查自己有没有停机
                                string sql自己停机 = $@"select * from scll where vbillcode = '{item.工单号}' and status = '停机' order by bus_date desc limit 1";
                                scll scll自己停机 = SqlSugarHelp.scope.Ado.SqlQuery<scll>(sql自己停机).FirstOrDefault();
                                if (scll自己停机 != null)
                                {
                                    //查自己有没有开机
                                    string sql自己开机 = $@"select * from scll where vbillcode = '{item.工单号}' and status = '开机' and bus_date > '{scll自己停机.bus_date}' order by bus_date desc limit 1";
                                    scll scll自己开机 = SqlSugarHelp.scope.Ado.SqlQuery<scll>(sql自己开机).FirstOrDefault();
                                    if (scll自己开机 != null)
                                    {
                                        string sqlR2 = $"update VIEW_拉伸报表 set 工单状态 = '开工' where 工单号 = '{item.工单号}';";
                                        int r2 = SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand(sqlR2);
                                    }
                                    else
                                    {
                                        string sqlR2 = $"update VIEW_拉伸报表 set 工单状态 = '停机' where 工单号 = '{item.工单号}';";
                                        int r2 = SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand(sqlR2);
                                    }
                                }
                                else
                                {
                                    string sqlR2 = $"update VIEW_拉伸报表 set 工单状态 = '开工' where 工单号 = '{item.工单号}';";
                                    int r2 = SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand(sqlR2);
                                }
                            }
                        }

                    }
                    if (item.产线 == "2")
                    {
                        string sql自己状态 = $@"select * from scll二线 where vbillcode = '{item.工单号}' and status not like '更改配方%' order by bus_date desc limit 1";
                        scll二线 scll自己 = SqlSugarHelp.scope.Ado.SqlQuery<scll二线>(sql自己状态).FirstOrDefault();

                        if (SqlSugarHelp.scope.Queryable<scll二线>().Where(it => it.vbillcode == item.工单号).Any() == false) //没有材料出库单了 就是未开工
                        {
                            string sqlR2 = $"update VIEW_拉伸报表 set 工单状态 = '未开工' where 工单号 = '{item.工单号}';";
                            int r2 = SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand(sqlR2);
                        }
                        else if (SqlSugarHelp.scope.Queryable<scll二线>().Where(it => it.vbillcode != item.工单号 && it.bus_date > scll自己.bus_date).Any() == true) //有比自己大的材料出库单了 就是结束
                        {
                            string sqlR2 = $"update VIEW_拉伸报表 set 工单状态 = '结束' where 工单号 = '{item.工单号}';";
                            int r2 = SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand(sqlR2);
                        }
                        else
                        {
                            //查上一个工单有没有停机状态
                            string sql上一工单 = $@"select * from scll二线 where vbillcode <> '{item.工单号}'  and bus_date < '{scll自己.bus_date}' order by bus_date desc limit 1";
                            scll二线 scll上一工单 = SqlSugarHelp.scope.Ado.SqlQuery<scll二线>(sql上一工单).FirstOrDefault();
                            //查上一单有没有停机状态
                            string sql上一工单停机 = $@"select * from scll二线 where vbillcode = '{scll上一工单.vbillcode}' and status = '停机' order by bus_date desc limit 1";
                            scll二线 scll上一工单停机 = SqlSugarHelp.scope.Ado.SqlQuery<scll二线>(sql上一工单停机).FirstOrDefault();
                            if (scll上一工单停机 != null)
                            {
                                //查这单有没有传开机
                                string sql上一工单开机 = $@"select * from scll二线 where vbillcode = '{scll上一工单.vbillcode}' and status = '开机' and bus_date >= '{scll上一工单停机.bus_date}' order by bus_date desc limit 1";
                                scll二线 scll上一工单开机 = SqlSugarHelp.scope.Ado.SqlQuery<scll二线>(sql上一工单开机).FirstOrDefault();
                                if (scll上一工单开机 != null)
                                {
                                    //如果有开机，就是开工
                                    //m1.status = "停机";
                                    string sqlR2 = $"update VIEW_拉伸报表 set 工单状态 = '开工' where 工单号 = '{item.工单号}';";
                                    int r2 = SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand(sqlR2);
                                }
                                else
                                {
                                    //查自己有没有开机
                                    string sql自己开机 = $@"select * from scll二线 where vbillcode = '{item.工单号}' and status = '开机' order by bus_date desc limit 1";
                                    scll二线 scll自己开机 = SqlSugarHelp.scope.Ado.SqlQuery<scll二线>(sql自己开机).FirstOrDefault();
                                    if (scll自己开机 != null)
                                    {
                                        //如果没有开机，就是停机
                                        //m1.status = "停机";
                                        string sqlR2 = $"update VIEW_拉伸报表 set 工单状态 = '开工' where 工单号 = '{item.工单号}';";
                                        int r2 = SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand(sqlR2);
                                    }
                                    else
                                    {
                                        //如果没有开机，就是停机
                                        //m1.status = "停机";
                                        string sqlR2 = $"update VIEW_拉伸报表 set 工单状态 = '停机' where 工单号 = '{item.工单号}';";
                                        int r2 = SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand(sqlR2);
                                    }

                                }
                            }
                            else
                            {
                                //查自己有没有停机
                                string sql自己停机 = $@"select * from scll二线 where vbillcode = '{item.工单号}' and status = '停机' order by bus_date desc limit 1";
                                scll二线 scll自己停机 = SqlSugarHelp.scope.Ado.SqlQuery<scll二线>(sql自己停机).FirstOrDefault();
                                if (scll自己停机 != null)
                                {
                                    //查自己有没有开机
                                    string sql自己开机 = $@"select * from scll二线 where vbillcode = '{item.工单号}' and status = '开机' and bus_date >= '{scll自己停机.bus_date}' order by bus_date desc limit 1";
                                    scll二线 scll自己开机 = SqlSugarHelp.scope.Ado.SqlQuery<scll二线>(sql自己开机).FirstOrDefault();
                                    if (scll自己开机 != null)
                                    {
                                        string sqlR2 = $"update VIEW_拉伸报表 set 工单状态 = '开工' where 工单号 = '{item.工单号}';";
                                        int r2 = SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand(sqlR2);
                                    }
                                    else
                                    {
                                        string sqlR2 = $"update VIEW_拉伸报表 set 工单状态 = '停机' where 工单号 = '{item.工单号}';";
                                        int r2 = SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand(sqlR2);
                                    }
                                }
                                else
                                {
                                    string sqlR2 = $"update VIEW_拉伸报表 set 工单状态 = '开工' where 工单号 = '{item.工单号}';";
                                    int r2 = SQLSERVERSqlSugarHelp.scope.Ado.ExecuteCommand(sqlR2);
                                }
                            }
                        }
                    }
                }
            }
        }
        #endregion

        #region 副产品生成
        private void button开始副产品任务_Click(object sender, EventArgs e)
        {
            if (int.TryParse(textBox副产品时间.Text, out int time) == true)
            {
                this.timer副产品.Interval = time * 60 * 1000;
                this.timer副产品.Enabled = true;
            }
            else
            {
                this.timer副产品.Enabled = false;
                MessageBox.Show("请输入正确的时间");
            }
        }
        private void button副产品生成测试_Click(object sender, EventArgs e)
        {
            //查NC当月工单
            查NC当月工单();
            //根据工单查入库的卷数
            查NC正品入库卷数();
            查NC材料出库数();
        }

        private void 查NC材料出库数()
        {
            string sql = "select * from nc副产品计算 where DATE_FORMAT(bus_date, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m');";
            List<nc副产品计算> nc = SqlSugarHelp.scope.Ado.SqlQuery<nc副产品计算>(sql);
            List<string> 工单号List = nc.Select(it => it.vbillcode).ToList();
            string inStrs = string.Join("','", 工单号List);
            string sqlNC材料出库数量 = $@"select vproductbatch vbillcode,sum(nnum) NC材料出库数量 from ic_material_b
                        where pk_org = '0001A11000000000Q5NJ'
                        and dr = 0
                        and vproductbatch in ('{inStrs}')
                        group by vproductbatch";
            List<nc副产品计算> nc材料出库数量 = OraSqlSugarHelp.scope.Ado.SqlQuery<nc副产品计算>(sqlNC材料出库数量);
            //更新
            if (nc材料出库数量 != null && nc材料出库数量.Count > 0)
            {
                var r = SqlSugarHelp.scope.Updateable(nc材料出库数量).UpdateColumns(it => new { it.NC材料出库数量 }).WhereColumns(it => it.vbillcode).ExecuteCommand();
            }

            string sql归零 = "update nc副产品计算 set 倒算副产品重量 = 0 ,副产品比例= 0 ,可以推副产品 = '不可以'  where DATE_FORMAT(bus_date, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m');";
            int r0 = SqlSugarHelp.scope.Ado.ExecuteCommand(sql归零);

            //计算副产品重量
            string sql倒算副产品重量 = "update nc副产品计算 set 倒算副产品重量 = (IFNULL(NC材料出库数量,0) - IFNULL(NC主产品重量,0) - IFNULL(NC废膜重量,0)) where (NC主产品卷数 + PLC废膜卷数) >= PLC下线卷数";
            int r2 = SqlSugarHelp.scope.Ado.ExecuteCommand(sql倒算副产品重量);
            string sql计算副产品比率 = "update nc副产品计算 set 副产品比例 = 倒算副产品重量/IFNULL(NC材料出库数量,0) where IFNULL(NC材料出库数量,0) > 0";
            int r3 = SqlSugarHelp.scope.Ado.ExecuteCommand(sql计算副产品比率);
            string sql可以推副产品 = "update nc副产品计算 set 可以推副产品 = '可以' where 倒算副产品重量 > 0 and 副产品比例 < 0.8";
            int r可以推副产品 = SqlSugarHelp.scope.Ado.ExecuteCommand(sql可以推副产品);
        }

        private void 查NC正品入库卷数()
        {
            //查当月的数据
            DateTime currentDate = DateTime.Now;
            int currentYear = currentDate.Year;
            int currentMonth = currentDate.Month;
            string sql = "select * from nc副产品计算 where DATE_FORMAT(bus_date, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m');";
            List<nc副产品计算> nc = SqlSugarHelp.scope.Ado.SqlQuery<nc副产品计算>(sql);
            if (nc != null && nc.Count > 0)
            {
                // nc 按 lineno分组
                var group = nc.GroupBy(it => it.lineno).ToList();
                //遍历group
                foreach (var item in group)
                {//工单号
                    List<string> 工单号List = item.Select(it => it.vbillcode).ToList();
                    string inStrs = string.Join("','", 工单号List);
                    string sql下线卷 = "";
                    if (item.Key == "1")
                    {
                        //根据工单号查正品入库卷数 重量
                        sql下线卷 = $@"select vbillcode,count(1) PLC下线卷数,sum(NetWeight)PLC下线重量  from wgrk where status = '完工' and vbillcode in ('{inStrs}') group by vbillcode";

                    }
                    else
                    {
                        sql下线卷 = $@"select vbillcode,count(1) PLC下线卷数,sum(NetWeight)PLC下线重量  from wgrk二线 where status = '完工' and vbillcode in ('{inStrs}') group by vbillcode";

                    }
                    List<nc副产品计算> plc下线卷数 = SqlSugarHelp.scope.Ado.SqlQuery<nc副产品计算>(sql下线卷);
                    //更新
                    if (plc下线卷数 != null && plc下线卷数.Count > 0)
                    {
                        var r = SqlSugarHelp.scope.Updateable(plc下线卷数).UpdateColumns(it => new { it.PLC下线卷数, it.PLC下线重量 }).WhereColumns(it => it.vbillcode).ExecuteCommand();
                    }
                }

                //查出所有的工单号
                List<string> 工单号s = nc.Select(it => it.vbillcode).ToList();
                string inStr = string.Join("','", 工单号s);
                //根据工单号查正品入库卷数 重量
                string sql正品 = $@"select vbmobillcode vbillcode,count(1) NC主产品卷数,sum(nbwrnum) NC主产品重量
                                    from mm_wr
                                    left join mm_wr_product on mm_wr_product.pk_wr = mm_wr.pk_wr
                                    left join bd_material on bd_material.pk_material = mm_wr_product.cbmaterialvid
                                    where mm_wr.pk_org = '0001A11000000000Q5NJ'
                                    and mm_wr_product.dr = 0
                                    and vbmobillcode in ('{inStr}')
                                    and bd_material.code <> '35120003'
                                    group by vbmobillcode";

                List<nc副产品计算> nc正品入库 = OraSqlSugarHelp.scope.Ado.SqlQuery<nc副产品计算>(sql正品);
                //更新
                if (nc正品入库 != null && nc正品入库.Count > 0)
                {
                    var r = SqlSugarHelp.scope.Updateable(nc正品入库).UpdateColumns(it => new { it.NC主产品卷数, it.NC主产品重量 }).WhereColumns(it => it.vbillcode).ExecuteCommand();
                }

                //根据工单号查废膜数量
                string sql废膜 = $@"select vbmobillcode vbillcode,count(1) NC废膜卷数,sum(nbwrnum) NC废膜重量
                                    from mm_wr
                                    left join mm_wr_product on mm_wr_product.pk_wr = mm_wr.pk_wr
                                    left join bd_material on bd_material.pk_material = mm_wr_product.cbmaterialvid
                                    where mm_wr.pk_org = '0001A11000000000Q5NJ'
                                    and mm_wr_product.dr = 0
                                    and vbmobillcode in ('{inStr}')
                                    and bd_material.code = '35120003'
                                    group by vbmobillcode";
                List<nc副产品计算> nc废膜入库 = OraSqlSugarHelp.scope.Ado.SqlQuery<nc副产品计算>(sql废膜);
                //更新
                if (nc废膜入库 != null && nc废膜入库.Count > 0)
                {
                    var r = SqlSugarHelp.scope.Updateable(nc废膜入库).UpdateColumns(it => new { it.NC废膜卷数, it.NC废膜重量 }).WhereColumns(it => it.vbillcode).ExecuteCommand();
                }

                //查PLC废膜数量和重量
                string sqlPLC废膜 = $@"
                                    select vbillcode,COUNT(1) PLC废膜卷数,sum(quantity) PLC废膜重量
                                    from wgrk_film
                                    where vbillcode in ('{inStr}')
                                    group by vbillcode";

                sqlPLC废膜 = $@"
                            select wgrk.vbillcode,COUNT(1) PLC废膜卷数,sum(wgrk_film.quantity) PLC废膜重量
                                from wgrk_film left join wgrk
                            on wgrk.guid = wgrk_film.guid
                            where wgrk.vbillcode in ('{inStr}')
                            group by vbillcode";
                List<nc副产品计算> plc废膜 = SqlSugarHelp.scope.Ado.SqlQuery<nc副产品计算>(sqlPLC废膜);
                //更新
                if (plc废膜 != null && plc废膜.Count > 0)
                {
                    var r = SqlSugarHelp.scope.Updateable(plc废膜).UpdateColumns(it => new { it.PLC废膜卷数, it.PLC废膜重量 }).WhereColumns(it => it.vbillcode).ExecuteCommand();
                }


            }
        }

        private void 查NC当月工单()
        {
            string sql = $@"select SYS_GUID() guid ,to_date(mm_pmo.dbilldate,'yyyy-mm-dd hh24:mi:ss') bus_date,mm_pmo.vbillcode vbillcode,
                            CASE
                            WHEN mm_mo.cdeptid LIKE '1001A110000000V585T3' THEN '1'
                            WHEN mm_mo.cdeptid LIKE '1001A110000000V58DLM' THEN '2'
                            ELSE mm_mo.cdeptid
                            END AS lineno, materialtype AS 产品型号,'0' resultcode
                            from mm_pmo  join mm_mo on  mm_mo.cpmohid = mm_pmo.CPMOHID 
                            left join bd_material on bd_material.pk_material = mm_mo.cmaterialid
                            where mm_mo.dr=0  and mm_pmo.pk_org = '0001A11000000000Q5NJ'
                            and to_char(to_date(mm_pmo.dbilldate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm')=to_char(sysdate,'yyyy-mm')";
            List<nc副产品计算> nc = OraSqlSugarHelp.scope.Ado.SqlQuery<nc副产品计算>(sql);
            if (nc != null && nc.Count > 0)
            {
                nc.ForEach(it => { it.guid = Guid.NewGuid().ToString(); it.可以推副产品 = "不可以"; });
                //插入VIEW_拉伸报表，如果工单号存在就不插入
                var storage = SqlSugarHelp.scope.Storageable(nc)
                                      .WhereColumns(it => it.vbillcode)//指定一个条件，当然支持多个 new {it.id,it.name}
                                      .ToStorage();//将数据进行分组
                int r = storage.AsInsertable.ExecuteCommand();
            }
        }

        #endregion

        private void button拉伸报表定时任务_Click(object sender, EventArgs e)
        {
            if (int.TryParse(textBox拉伸时间.Text, out int time) == true)
            {
                this.timer拉伸报表.Interval = time * 60 * 1000;
                this.timer拉伸报表.Enabled = true;
            }
            else
            {
                this.timer拉伸报表.Enabled = false;
                MessageBox.Show("请输入正确的时间");
            }
        }

        private void timer拉伸报表_Tick(object sender, EventArgs e)
        {
            try
            {
                timer拉伸报表.Enabled = false;
                logger.Info($"拉伸报表开始");
                查投产工单();
                //查投产的工单
                更新工单状态();
                查NC工单();
                计算工单用料产量();
                //查mes称重记录
                查MES称重记录();
                logger.Info($"拉伸报表结束");
            }
            catch (Exception ex)
            {
                logger.Info($"拉伸成品率任务异常{ex.StackTrace}");
            }
            finally
            {
                timer拉伸报表.Enabled = true;
                label拉伸成品率.Text = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            }
        }

        private void timer副产品_Tick(object sender, EventArgs e)
        {
            try
            {
                timer副产品.Enabled = false;
                logger.Info($"副产品任务开始");
                //查NC当月工单
                查NC当月工单();
                //根据工单查入库的卷数
                查NC正品入库卷数();
                查NC材料出库数();
                logger.Info($"副产品任务结束");
            }
            catch (Exception ex)
            {
                logger.Info($"副产品任务异常{ex.StackTrace}");
            }
            finally
            {
                timer副产品.Enabled = true;
                label副产品.Text = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            }
        }

        private void 删除30天前的的文件() {

            string path = @"E:\WMSNCPort\log";
            if (Directory.Exists(path))
            {
                DirectoryInfo dir = new DirectoryInfo(path);
                FileInfo[] files = dir.GetFiles();
                foreach (FileInfo file in files)
                {
                    if (file.CreationTime < DateTime.Now.AddDays(-7))
                    {
                        file.Delete();
                    }
                }
            }
            path = @"E:\WMSNCPort\logs";
            if (Directory.Exists(path))
            {
                DirectoryInfo dir = new DirectoryInfo(path);
                FileInfo[] files = dir.GetFiles();
                foreach (FileInfo file in files)
                {
                    if (file.CreationTime < DateTime.Now.AddDays(-7))
                    {
                        file.Delete();
                    }
                }
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //删除30天前的的文件();
        }

        private void timer删文件_Tick(object sender, EventArgs e)
        {
            //删除30天前的的文件();
        }
    }
    
}
